phil phil - 10 days ago 5
SQL Question

SQL selecting row in table that matches n number of rows in another table

I'm fairly new using SQL and perhaps there is a better way to phrase this problem, but can be clearly be described using the following example...

In a video game database, I have a player table with multiple accounts:

create table Player (
login varchar(15) not null,
name varchar(15) not null,
gender char(1) not null,
...
);


Each account has multiple avatars

create table Avatar (
login varchar(15) not null,
name varchar(15) not null,
gender char(1)
...
);


I am trying to query Player where ALL of their avatars match their gender. Hence, only if a player is male, and all of their avatars are also male, I want to include the row (and vice versa with female).

Answer
SELECT * FROM Player p WHERE NOT EXISTS(SELECT * FROM Avatar a WHERE a.login = p.login AND a.gender <> p.gender)

This should do the trick. Although it also matches Players where there is no Avatar at all.

SELECT * FROM Player p WHERE NOT EXISTS (SELECT * FROM Avatar a WHERE a.login = p.login AND a.gender <> p.gender) AND COUNT(SELECT * FROM Avatar a WHERE a.login = p.login) >= 1

... of course assuming that 'login' is the unique identifier for a user.