Querying for unused parent entries

GregStevens

So I have a table of teams, orgTeams, each of which has a primary key, teamID. I have a table of users, orgUsers, each of which has field that describes what team the user is on, userTeamID. If I want to get a list of all of the teams that have at least one person on them, it is easy: SELECT DISTINCT teamID FROM orgTeams,orgUsers WHERE userTeamID=teamID However, with people moving from team to team, sometimes teams end up "empty", i.e. with nobody at all on them. What query can I use to identify such "empty" teams?

Kiefie

To determine if a table has rows in it you can use SELECT COUNT(*) FROM tableName ie. ( I think) SELECT DISTINCT teamID FROM orgTeams,orgUsers WHERE userTeamID=teamID and COUNT(orgTeams.Person) > 0

Kiefie The man with a plan.

GregStevens

I understand the COUNT() function, but I don't understand what orgTeams.Person is supposed to refer to in your example. I don't have a "Person" field in my table orgTeams.

Pete OHanlon

You could probably do this with:

select distinct teamId
from orgTeams
left join orgUsers
on teamID = userTeamId
group by userTeamID, teamID
having count(userTeamID) = 0

Deja View - the feeling that you've seen this post before.

Mike Dimmick

select teamId
from orgTeams
left join orgUsers
on teamID = userTeamId
where userTeamId IS NULL

is equivalent and likely to execute quicker.

Stability. What an interesting concept. -- Chris Maunder

Kiefie

I don't know what fields you have in your table so I made it up. Could work with just about any field. I see there are more posts which will probably work better.

Kiefie The man with a plan.

Pete OHanlon

True - this is what happens when you code when tired. You end up overcomplicating things. Thanks for pointing this out to the OP. I'd like to think that I would have spotted this, this morning.

Deja View - the feeling that you've seen this post before.

GregStevens

Thank you! This worked perfectly.