MySQL Forums
Forum List  »  Views

(reverse) join
Posted by: Roger Ottani
Date: March 12, 2014 08:53AM

here 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
(reverse) join
5727
March 12, 2014 08:53AM
1760
March 12, 2014 09:42AM
1759
March 12, 2014 09:51AM
1816
March 12, 2014 11:48AM
1873
March 12, 2014 01:11PM
2024
March 13, 2014 03:23AM
1748
March 13, 2014 10:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.