sql - mySql - (reverse) Left Join from multiple tables and multiple relationship -

there table, serverog (service) wich releaded 4 tables serva, servb, servc, servd (they different non uniformable services) servtype (type of service) , type_id (numeric id 1 of 4 service table)

structure (simplyficaded):

serverog

mysql> select * serverog +----+-------+----------+------+ | idse | servtype | type_id |   +----+-------+----------+------+ | 1 | 1 | 1 | | 2 | 2 | 1 | | 3 | 4 | 1 | | 4 | 3 | 1 | | 5 | 1 | 2 | +----+-------+----------+-------+ 

serva

mysql> select * serva +----+-------+----------+------+ | idsa | service_code | type |  |+----+-------+----------+------+ | 1 | codice bla | 1 | | 2 | codice ecc | 1 | | 3 | bla bla | 1 | +----+-------+----------+------+ 

servb

mysql> select * servb +----+-------+----------+------+ | idsb | service_code | type |  +----+-------+----------+------+ | 1 | codice bla | 2 | | 2 | codice ecc | 2 | | 3 | bla bla | 2 | +----+-------+----------+------+ 

servc

mysql> select * servc +----+-------+----------+------+ | idsc | service_code | type |  +----+-------+----------+------+ | 1 | codice bla | 3 | | 2 | codice ecc | 3 | | 3 | bla bla | 3 | +----+-------+----------+------+ 

servd

mysql> select * servd +----+-------+----------+------+ | idsa | service_code | type |  +----+-------+----------+------+ | 1 | codice bla | 4 | | 2 | codice ecc | 4 | | 3 | bla bla | 4 | +----+-------+----------+------+ 

left join

select serverog.idse, serverog.servtype, serverog.typeid, serva.idsa, serva.type, servb.idsb, servb.type, serv.idsa, serv.type, servd.idsa, servd.type serverog  left join serva on serverog.servtype = serva.type , serva.idsa = serverog.typeid  left join servb on serverog.servtype = servb.type , servb.idsb = serverog.typeid  left join servc on serverog.servtype = servc.type , servc.idsc = serverog.typeid  left join servd on serverog.servtype = servd.type , servd.idsd = serverog.typeid  order serverog.idse  +----+-------+----------+------+------+------+---------+ | idse | servtype | type_id | idsa | idsb | idsc | idsd| +----+-------+----------+------+------+------+---------+ | 1 | 1 | 1 | 1 | null | null | null | | 2 | 2 | 1 | null | 1 | null | null | | 3 | 4 | 1 | null | null | null | 1 | | 4 | 3 | 1 | null | null | 1 | null | | 5 | 1 | 2 | 2 | null | null | null | +----+-------+----------+------+------+ 

this retur records releaded serverog. perfect!

now need show record serva, servb, servc, servd not present in serverog. inverse precedent join. i've tried right join, idse null without result

this looking example:

+----+-------+----------+------+ | idsa | idsb | idsc | idsd| +----+-------+----------+------+ | 3 | null | null | null |  | null | 2 | null | null |  | null | 3 | null | null |  | null | null |2 | null |  |null | null | 3 | null |  | null | null |null | 2 |  | null | null | null | 3 | +----+-------+----------+------+ 

you (cause don't see how have flatten list without union in case, maybe find someghint more elegant).

select   max(i.idsa)  idsa,  max(i.idsb)  idsb,  max(i.idsc)  idsc,  max(i.idsd)  idsd  (select se.idse idsa, null idsb, null idsc, null idsd serva s left join serverog se on se.servtype = s.type , s.idsa = se.typeid   union  select null , se.idse, null, null servb s left join serverog se on se.servtype = s.type , s.idsa = se.typeid   union  select null , null, se.idse, null servc s left join serverog se on se.servtype = s.type , s.idsa = se.typeid   union  select null ,  null, null, se.idse servd s join serverog se on se.servtype = s.type , s.idsa = se.typeid)