Search code examples
sqlmysqljoin

MySQL Join of 2 tables satisfying multiple join rules

I'm trying to figure out how to perform a pretty complex join on 2 tables that satisfy multiple rules. I'm using mysql 8.

Here is some sample SQL to work with:

create table person (
    id integer primary key
    name text not null
);

create table team (
    id integer primary key,
    person_id integer,
    org_id integer,
    champion integer
);

insert into person values (1, 'joe');
insert into person values (2, 'bill');
insert into person values (3, 'sally');
insert into person values (4, 'beth');
insert into person values (5, 'grace');

insert into team values (1, 1, 500, 0);
insert into team values (2, 2, 500, 0);
insert into team values (3, 2, 500, 0);
insert into team values (4, 3, 500, 1);
insert into team values (5, 1, 700, 1);
insert into team values (6, 1, 600, 0);
insert into team values (7, 2, 600, 0);
insert into team values (8, 4, 600, 1);
insert into team values (9, 3, 700, 0);
insert into team values (10, 4, 700, 1);

Here are the rules that I'm trying to follow:

I want a list of all persons satisfying these rules:

  1. If they do NOT belong to any team, I want them in the list

  2. If NONE of the teams that they belong to are champion teams, I want them in the list

  3. If ANY of the teams that a person belongs to is a "700" organization team, AND that team is a champion team, then I want them in the list

Thus, the query should result in this output (order does not matter):

ID NAME
5 grace 
2 bill 
4 beth 
1 joe

REASONING: grace: does not belong to any team bill: none of the his teams are champion teams beth: belongs to a 700 team which is also a champion team joe: belongs to a 700 team which is also a champion team

"sally" is not in the list because one of her teams is a champion team, and that champion team is not a 700 organization team.

Solution

  • I think your sample data might be wrong. for the condition:

    • If ANY of the teams that a person belongs to is a "700" organization team, AND that team is a champion team, then I want them in the list

    wouldn't joe be in the list? Since: insert into team values (5, 1, 700, 1);

    Anyway here is my solution which does return joe in the list:

    SELECT DISTINCT Person.id, name FROM Person, team 
WHERE 
person.id NOT IN (
    SELECT person_id FROM team
) -- gets not in a team

OR  person.id NOT IN (
    SELECT person_id FROM team WHERE champion = 1
) -- gets not in a champion team

OR  person.id IN (
    SELECT person_id FROM team WHERE champion = 1 AND org_id=700
) -- gets in a 700 team and is a champion