Help me work out how to build my SQL for this JOIN scenario

Question

I'm still trying to learn SQL as of this writing, so I'm a bit shakey at the moment.

My situation is like this:

1. I have a table called 'Tasks' with an auto-incrementing primary key ID and a text field (and a few others that aren't relevant to this problem, too).
2. I have another table called 'Locations' with a foreign key referring to a task by ID and a text field representing the location's name. These map sets of locations to given specific tasks (one-to-many I think it's called).
3. I have a data structure in my code containing a list of location values. I want to query for tasks that have at least all of these locations associated with them.
4. I will have other tables with similar one-to-many relationships as well that I'll need to use as the basis of task querying. They might also be used to filter each others' queried results. How do I stack several of these kinds of filters within my SQL use (as opposed to ANDing between the result sets in my code, manually)?

It seems like it should be simple, but I suppose I simply lack imagination at the moment. There will be more of these sorts of problems for me down the road, so seeing an example of what solves this will help for those as well.

Answer 1

Try this:

Select TaskId, LocationName from Task, Location where
Task.TaskId = Location.TaskId and
LocationName in (<all the locations you want to query against>)

You can send a comma-separated list of locations and use in this query. If you want to make the query scalable then you can either create small SPs that return a list of valid/filtered and other SPs may use them as input. You can also handle this is on the coding side (not sql).

Answer 2

What I get from 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)

Answer 3

i just tested a little thing:

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 have to do is set the number for the having claus.

this will only work if a task doesn't have the same Location twice or more.

u could even do a for every location an Exists clause, but only if its static