kxyz kxyz - 1 month ago 4
MySQL Question

Inner join 3 tables, retrieve if conditions

I have three tables

Person
- id
- other_fields

PersonTypes
- id
- person_id
- type_id

PersonEmails
- id
- person_id
- email_address


How to get only these email addresses where PersonTypes.type_id is different than
value
?
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
select P1.id,
       P2.type_id, -- just for the sake of this output
       P2.email_address
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

or

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