Help with Query to fnd missing records

[Jenny6157]

I am working on query to determine if there are missing records in one table, by comparing two tables. Here is a simple example of this below. I am having an issue with it not distiguishing finding any records verses needing all of them to be present to get a "Yes" result..

 

Table 1 – Team and Members

 

Team Member

 

Blue Becky

Blue Tom

Blue George

Red Sam

Red Mike

 

Table 2 - Members

 

Member

 

Becky

Tom

George

Sam

 

 

Statement results should return Team name with a Yes - If all members exist in Table 2 that are present in tables one, No - If not all members from Table 1 exist in Table 2

 

In the example above the results would be:

 

Team All_Members_Present

 

Blue Yes

Red No

 

[niche]

This kinda looks like a school question. Have you done the sql tuts?

 

http://www.w3schools.com/sql/default.asp

 

Else, have you tried writing any code so far? If so, please post it.

 

EDIT:

 

Give extra attention to the the JOIN keyword.

Edited October 30, 2014 by niche

[Jenny6157]

No, not a school quesiton:). I'm too old to go to school...lol..The real example is mucho more complicated..I'm just simplifying...

[niche]

What do you have so far?

 

EDIT: Think subquery. That's how I think I'd start.

Edited October 30, 2014 by niche

[justsomeguy]

While not meeting your specific requirements, you could use something like this to just list all records from table 1 where the member is not in table 2:

SELECT * FROM table1 AS t1 WHERE NOT EXISTS (SELECT * FROM table2 AS t2 WHERE t2.member = t1.member)

I believe that a NOT EXISTS query would generally be faster there than a NOT IN query. This is the NOT IN query:

SELECT * FROM table1 AS t1 WHERE member NOT IN (SELECT member FROM table2)