Mysql Question posted in
The official MySQL documentation can be found here.

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

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" is not in the list because one of his teams is a champion team, and he is not in the 700 organization. "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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    Here is my query too.

    select distinct p.* from Person p
left join Team t
on t.person_id = p.id
where t.id is null 
or (select COUNT(*) from Team where person_id = p.id and champion = 1) = 0
or (select COUNT(*) from Team where person_id = p.id and champion = 1 and org_id = 700) > 0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search