CODEWITHSUNDEEP
sqldb2
phil
phil

Reputation: 525

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).

Upvotes: 0

Views: 52

Answers (2)

Florian Heer
Florian Heer

Reputation: 704

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.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93704

Use Group By and Having clause. Filter the names with distinct count of gender is 1 in Having clause.

select name 
from Avatar 
Group by name 
Having count(distinct gender) = 1

or 

Having Min(gender) = Max(gender)

Upvotes: 1

Related Questions