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

there is a table, ServErog (service) wich is releaded to 4 tables ServA, ServB, ServC, ServD (they are different non uniformable services) with servtype (type of service) and with type_id (numeric id from one of the 4 service table)

Structure (simplyficaded):

ServErog

mysql> select * from ServErog
+----+-------+----------+------+
| idSE | servtype | type_id | 
 +----+-------+----------+------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 4 | 1 |
| 4 | 3 | 1 |
| 5 | 1 | 2 |
+----+-------+----------+-------+

ServA

mysql> select * from ServA
+----+-------+----------+------+
| idSA | service_code | type | 
|+----+-------+----------+------+
| 1 | codice bla | 1 |
| 2 | codice ecc | 1 |
| 3 | bla bla | 1 |
+----+-------+----------+------+

ServB

mysql> select * from ServB
+----+-------+----------+------+
| idSB | service_code | type | 
+----+-------+----------+------+
| 1 | codice bla | 2 |
| 2 | codice ecc | 2 |
| 3 | bla bla | 2 |
+----+-------+----------+------+

ServC

mysql> select * from ServC
+----+-------+----------+------+
| idSC | service_code | type | 
+----+-------+----------+------+
| 1 | codice bla | 3 |
| 2 | codice ecc | 3 |
| 3 | bla bla | 3 |
+----+-------+----------+------+

ServD

mysql> select * from 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
From
ServErog 
Left Join
ServA On ServErog.servtype = ServA.type And ServA.idSA = ServErog.typeid 
Left Join
ServB On ServErog.servtype = ServB.type And ServB.idSB = ServErog.typeid 
Left Join
ServC On ServErog.servtype = ServC.type And ServC.idSC = ServErog.typeid 
Left Join
ServD On ServErog.servtype = ServD.type And ServD.idSD = ServErog.typeid 
Order By
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 all records releaded with ServErog. Perfect!

Now I need to show all record from ServA, ServB, ServC, ServD NOT PRESENT in ServErog. Like an inverse the precedent Join. I've tried with right join, with idSE is null but without result

This is what I looking for this 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 |
+----+-------+----------+------+

Answers


you could do something like this (cause I don't see how to have a flatten list without UNION in that case, maybe someone will find someghint more elegant).

select 
 max(i.idSA)  as idSA,
 max(i.idSB)  as idSB,
 max(i.idSC)  as idSC,
 max(i.idSD)  as idSD
FROM

(select se.idSE as idSA, null as idSB, null as idSC, null as idSD
FROM ServA s
left join ServErog se On se.servtype = s.type And s.idSA = se.typeid 

UNION

select null , se.idSE, null, null
FROM ServB s
left join ServErog se On se.servtype = s.type And s.idSA = se.typeid 

UNION

select null , null, se.idSE, null
FROM ServC s
left join ServErog se On se.servtype = s.type And s.idSA = se.typeid 

UNION

select null ,  null, null, se.idSE
FROM ServD s
join ServErog se On se.servtype = s.type And s.idSA = se.typeid) i

Need Your Help

Windows 8 package.appxmanifest Can't add my own logo

c# visual-studio windows-8 windows-8.1 windows-store

I exported Construct2 project to Windows 8 Store app. I have a problem with setting my own logo and splash screen. In file package.appxmanifest I want to change it but when I inserted my owns PNGs,...

Local Storage vs Cookies: save and send user credentials via websockets

javascript backbone.js cookies websocket local-storage

I have: websocket secure connection (wss) through the whole app. + Backbone.js on client side if it's significant.