kxyz kxyz - 9 months ago 38
MySQL Question

Inner join 3 tables, retrieve if conditions

I have three tables

- id
- other_fields

- id
- person_id
- type_id

- id
- person_id
- email_address

How to get only these email addresses where PersonTypes.type_id is different than
I'm using MariaDB v.10, I tried to use left outer joins with in all tables and then return only these rows where type_id is different. But I'm not sure about the results.

SELECT pe.email_address
FROM Person p
LEFT OUTER JOIN PersonTypes pt ON p.id = pt.person_id
LEFT OUTER JOIN PersonEmails pe ON p.id = pe.person_id
WHERE pt.type_id != 14;

Answer Source
select P1.id,
       P2.type_id, -- just for the sake of this output
from Person P1
inner join
    select PT.person_id, PT.type_id, PE.email_address
    from PersonTypes PT
    inner join PersonEmails PE
      on PE.person_id = PT.person_id
    where PT.type_id <> 14
    ) P2
  on P2.person_id = P1.id


select pe.email_address
from PersonEmails pe
where person_id not in 
    select person_id
    from PersonTypes
    where Type_id = 14