I am still learning SQL by this writing, so I am a bit shakey right now.
My situation is much like this:
- I've got a table known as 'Tasks' by having an auto-incrementing primary key ID along with a text area (along with a couple of others that are not highly relevant to this issue, too).
- I've another table known as 'Locations' having a foreign key mentioning to some task by ID along with a text area representing the location's title. These map teams of locations to given specific tasks (one-to-many I believe it's known as).
- I've got a data structure during my code that contains a listing of location values. I wish to query for tasks which have a minimum of many of these locations connected together.
- I'll produce other tables concentrating on the same one-to-many associations too that I'll want to use because the foundation of task querying. They may also be employed to filter each others' queried results. How do you stack a number of these types of filters inside my SQL use (instead of ANDing between your result takes hold my code, by hand)?
It appears like it ought to be simple, however i suppose I merely lack imagination right now. You will see greater number of these types of trouble for me in the future, so seeing a good example of what solves this helps for individuals too.
Select TaskId, LocationName from Task, Location where Task.TaskId = Location.TaskId and LocationName in (<all the locations you want to query against>)
You are able to send a comma-separated listing of locations and employ within this query. If you wish to result in the query scalable you'll be able to either create small SPs that return a listing of valid/strained along with other SPs could use them as input. You may also handle this really is around the coding side (not sql).
Things I get out of your description
declare @tasks table ( taskid int, taskname varchar(20) ) declare @locations table ( locationid int, taskid int, locationname varchar(20) ) insert into @tasks select 1, 'task1' insert into @tasks select 2, 'task2' insert into @tasks select 3, 'task3' insert into @locations select 1, 1, 'location1' insert into @locations select 2, 1, 'location2' insert into @locations select 3, 1, 'location3' insert into @locations select 4, 2, 'location4' insert into @locations select 5, 2, 'location5' insert into @locations select 6, 3, 'location6' select t.taskid, t.taskname, l.locationid, l.locationname from @tasks t inner join @locations l on t.taskid = l.taskid where l.locationname in ('location1', 'location4') -- OR locationid -- You can alos do this like select t.taskid, t.taskname, l.locationid, l.locationname from @tasks t inner join @locations l on t.taskid = l.taskid where l.locationname in (select top 2 locationname from @locations order by locationid desc)
i simply examined just a little factor:
SELECT * FROM Tasks WHERE ID IN ( SELECT l.ID FROM Location l WHERE l.Loc_name IN ('loc1','loc2','loc3') GROUP BY l.ID HAVING COUNT(l.Loc_name) = 3 -- Number of all location u have in the in clause );
what u need to do is placed the amount for that getting claus.
this can only work if your task does not have a similar Location two times or even more.
u may even perform a for each location an Is available clause, but only when its static