jep
-
Posts
5 -
Joined
-
Last visited
Posts posted by jep
-
-
I've already tried to make an UNION command but without result (how can put different name of columns in the four Select?)
-
Thank for answer Psycho!
You're right but I cant create (merge) the service tables (servA, servB, serC, servD) in one because they are very difference each other*.
servA contains information about course in a classroom
servB contains information about consultancyservC contains information about elearning
servD contains information about voucher
They have a lot of different field and my example question are a simplification of them with only some common field.
-
it's impossible or it's too simple?
-
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 |
+----+-------+----------+------+
(Outer) Join from multiple tables and multiple key
in MySQL Help
Posted
Ok Psycho,
now I've resolved the UNION clause and show what I need.
I'm thinking about your consideration and making some tests.
thank you!