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)
Comments
Post a Comment