(Outer) Join from multiple tables and multiple key

[jep]

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  |

+----+-------+----------+------+

Edited March 10, 2014 by Zane

[jep]

it's impossible or it's too simple?
 

[Psycho]

Why do you have separate tables for ServA, ServB, etc.? You include a column in each with is defining the 'type' already. All these records belong in ONE table such as this (let's just call it "Serv"):

 

 

+------+---------------+------+
| idS | service_code  | type |
+------+---------------+------+
|   1  | codice blaA   |  1   |
|   2  | codice eccA   |  1   |
|   3  | bla blaA      |  1   |
|   4  | codice blaB   |  2   |
|   5  | codice eccB   |  2   |
|   6  | bla blaB      |  2   |
|   7  | codice blaC   |  3   |
|   8  | codice eccC   |  3   |
|   9  | bla blaC      |  3   |
|   10 | codice blaD   |  4   |
|   11 | codice eccD   |  4   |
|   12 | bla blaD      |  4   |
+------+---------------+------+

 

Then your original query will get all the associated records in a much simpler way

 

SELECT ServErog.idSE, ServErog.servtype, ServErog.typeid,
       Serv.idS, Serv.type
FROM ServErog
LEFT JOIN Serv
  On ServErog.servtype = Serv.type AND ServErog.typeid = Serv.idS
ORDER BY ServErog.idSE, Serv.type

 

You can determine in the processing which types are included and which ones are not. Then to get all the ones not associated with a record in ServErog you would run something like this

 

SELECT Serv.idS, Serv.service_code, Serv.type
FROM Serv
LEFT JOIN ServErog
  On ServErog.servtype = Serv.type AND ServErog.typeid = Serv.idS
WHERE ServErog.idSE IS NULL

Or, if you wanted the ones not associated with a specific ServErog record

 

SELECT Serv.idS, Serv.service_code, Serv.type
FROM Serv
LEFT JOIN ServErog
  On ServErog.servtype = Serv.type AND ServErog.typeid = Serv.idS
 AND ServErog.idSE = $id
WHERE ServErog.idSE IS NULL

 

With what you have, to get all the records from four tables in a single result set would take a more complicated UNION type query. Maybe something like:

 

SELECT ServA.idS, ServA.service_code, ServA.type
FROM ServA
LEFT JOIN ServErog
  On ServErog.servtype = Serv.type AND ServErog.typeid = Serv.idS
WHERE ServErog.idSE IS NULL

UNION

SELECT ServA.idS, ServA.service_code, ServA.type
FROM ServA
LEFT JOIN ServErog
  On ServErog.servtype = Serv.type AND ServErog.typeid = Serv.idS
WHERE ServErog.idSE IS NULL

UNION

SELECT ServA.idS, ServA.service_code, ServA.type
FROM ServA
LEFT JOIN ServErog
  On ServErog.servtype = Serv.type AND ServErog.typeid = Serv.idS
WHERE ServErog.idSE IS NULL

UNION

SELECT ServA.idS, ServA.service_code, ServA.type
FROM ServA
LEFT JOIN ServErog
  On ServErog.servtype = Serv.type AND ServErog.typeid = Serv.idS
WHERE ServErog.idSE IS NULL

 

When you have to create such complicated queries to get what should be something simple, it is an indication that the DB structure is not right. Put all the Serv records into a single table.

[jep]

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 consultancy

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

[jep]

I've already tried to make an UNION command but without result (how can put different name of columns in the four Select?)

[Psycho]

UNION queries have specific requirements such as each query must return the same number of fields and each field must be the same 'type". You need to make sure to create them to meet that requirement.

 

 

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 consultancy

servC contains information about elearning

servD contains information about voucher

 

Yes, you can merge the 'common' data for those four tables. You could leave those four tables exactly as they are, but remove the service_code value. Then use a single the Serv table as I showed above. That way you have one table for all the common data which you can use for JOINing on the ServErog table and can additionally JOIN out to those individual table for the additional info. I can't really know what the best way is to set these up without understanding the entire project (which I don't want to do), but am only saying that what you just stated doesn't preclude you from doing it that way.

[jep]

 

 

...(which I don't want to do)...

 

 

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!