Multiple table query 2

[aliashippysmom]

Hi! I'm sure this isn't too difficult, but I'm having a problem with it. Maybe I've looked at it too much.
Here is my data:
Staff Table: staff_id (key), staff_name
Group Table: group_id (key), group_name
Staff_Group: group_id, staff_id Links staff people to their group. People can belong to more than one group.

I want to write a query to pull all staff who belong to BOTH Group A and Group B, say.

I've tried using an IN clause but that gave me people who belong to Group A or Group B.

Thanks!

 

[jbenson001]

....Where group_Id = 1 AND groupID = 2

 

[gmmastros]

Where group_Id = 1 AND groupID = 2

That's not going to work. The GroupId cannot be 1 AND 2 for the same row.

aliashippysmom,

Can you show us what you've tried so far?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
Doug needs a new pair of shoes, please click an ad link.

 

[ESquared]

>> Where group_Id = 1 AND group_ID = 2

This means group_ID has to be two different numbers at the same time...

Where group_Id = 1 [b][COLOR=black yellow]OR[/color][/b] group_ID = 2

 

[aliashippysmom]

Thanks for your reply. I'm still having a problem. Here is an example. Here are 4 records from the Staff_Group table showing the groups for one person:
staff_id group_id
292 1
292 4
292 7
292 12

I want to write a query to retrieve this record and the parent Staff record (staff_id = 292) when the staff person belongs to TWO groups, say group_id 4 and 12

This query does not work:
SELECT *
FROM dbo.Staff_Group, Staff
where staff_group.staff_id = Staff.staff_id
and staff_group.group_id = 4 and staff_group.group_id = 12

OR WRITTEN IN THE ALTERNATIVE WAY:
SELECT *
FROM dbo.Staff_Group INNER JOIN
Staff ON dbo.Staff_Group.staff_id = Staff.staff_id
WHERE (dbo.Staff_Group.group_id = 4) AND (dbo.Staff_Group.group_id = 12)

I get no records, of course.
Hope this makes sense.

 

[SQLSister]

have you tried

SELECT     *
FROM         dbo.Staff s INNER JOIN
                      Staffgroup sg1 ON dbo.Sg1.staff_id = s.staff_id
					INNER JOIN
                      Staffgroup sg2 ON dbo.Sg2.staff_id = s.staff_id
WHERE     sg1.group_id = 4 AND sg2.group_id = 12

BTW do not ever use select * espcially if you have a join as it is inefficient (the join field is repeated). Specify the fields you need and only those fields.

Also you should stop using the join sysntax in this example:

SELECT     *
FROM         dbo.Staff_Group, Staff
where staff_group.staff_id = Staff.staff_id
and staff_group.group_id = 4 and staff_group.group_id = 12

It will lead to problems as you get into more complex queries because the join fields and the real where clauses are mixed together and it will not work properly at all if you need a left or right join. It is also much more difficult to maintain. Also it makes a lot of code to fix when Microsoft stops supporting the syntax as they have threatened to do in the past. Anybody know if they are not supporting this syntax in SQL Server 2008?

"NOTHING is more important in a database than integrity." ESquared

 

[PWise]

try

select   staff_id , staff_name
from [Staff Table]
inner join Staff_Group groupa
on groupa.staff_id=[Staff Table].staff_id
and groupa.group_id = 4 
inner join Staff_Group groupb
on groupb.staff_id=[Staff Table].staff_id
and groupb.group_id = 12

 

[jbenson001]

yeah.. sorry too quick of an answer.. forgot to mention the group by part.

 

[aliashippysmom]

Thanks to everyone who replied. You guys are great! I was really perplexed by this and under a time crunch, too. Thank you for the advice, too.