CODEWITHSUNDEEP
mysql
thesimon
thesimon

Reputation: 34

MySQL Join 3 tables by 2 columns ids in the first table

I have 3 tables... Contacts, Outbound Operator, Digital Operator. The contacts table contains all the contacts generated by either outbound operator and digital operator. So I will have these columns in "Contacts":

id, name, surname, id_outbound_operator, id_digital operator

In the second table I have all the outbound operators so the table is something like this:

uid, out_bound_operator_full_name

The third table is the same as the second but with these columns:

uid, digital_operator_full_name

I want to obtain something like this:

id, name, surname, outbound_operator_full_name (if this was generated by a outbound operator), digital_operator_full_name (if this was generated by a outbound operator).

I have to specify that in contacts table at lease one of the two 

(id_outbound_operator/id_digital_operator) is not null

I tried this 

SELECT CONTATTI.id, CONTATTI.nome_azienda, CONTATTI.telefono, CONTATTI.stato, CONTATTI.id_outbound, CONTATTI.id_digital_marketing_op, CONTATTI.blacklisted, OUTBOUND_INT_login.uid, OUTBOUND_INT_login.nome_completo, MARKETING_DIGITAL_login.uid, MARKETING_DIGITAL_login.nome_completo    
            FROM CONTATTI 
            JOIN OUTBOUND_INT_login 
            ON CONTATTI.id_outbound = OUTBOUND_INT_login.uid 
            JOIN MARKETING_DIGITAL_login
            ON CONTATTI.id_digital_marketing_op = MARKETING_DIGITAL_login.uid

but it doesn't work properly

Upvotes: 0

Views: 37

Answers (2)

GMB
GMB

Reputation: 222482

You can join the three tables as follows. You want left join so that contact records that cannot joined with both outbound_operator and digital_operator are not eliminated from the resultset.

select 
    c.id, 
    c.name, 
    c.surname, 
    op.outbound_operator_full_name,
    do.digital_operator_full_name
from contact c
left join outbound_operator op on c.id_outbound_operator = op.uid
left join digital_operator do on c.id_digital_operator = do.uid

As commented by Uueerdo, if you want a unique column with either the outbound_operator_full_name or the digital_operator_full_name, then:

select 
    c.id, 
    c.name, 
    c.surname, 
    coalesce(op.outbound_operator_full_name, do.digital_operator_full_name) operator_full_name
from contact c
left join outbound_operator op on c.id_outbound_operator = op.uid
left join digital_operator do on c.id_digital_operator = do.uid

The advantage of this approach is that it requires a single scan on contact, vs two scans when using UNION (ALL).

Upvotes: 0

Barmar
Barmar

Reputation: 781004

You should use a UNION of queries that join with each of the other tables.

SELECT c.id, c.name, c.surname, o.outbound_operator_full_name
FROM CONTATTI AS c
JOIN OUTBOUND_INT_login AS o ON c.id = o.uid

UNION ALL

SELECT c.id, c.name, c.surname, m.digital_operator_full_name
FROM CONTATTI AS c
JOIN MARKETING_DIGITAL_login AS m ON c.id = m.uid

Upvotes: 1

Related Questions