Hi to everyone and sorry for my bad english...
there is a table, ServErog (service) wich is releaded to other 4 tables ServA, ServB, ServC, ServD (they are different non uniformable services) with two key, servtype (type of service) and 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.
It's like an inverse of 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 |
+----+-------+----------+------+