CODEWITHSUNDEEP
mysqljoinleft-joininner-joinouter-join
Cassandra
Cassandra

Reputation: 284

Select/Join multiple fields from different tables with same column name

I have one key table, a number of data tables with same column names in them, and one users table. I am trying to select values from the key table, join this output with some selected values from each of the data tables by unique ID (uaID) to the entries selected already from the key table (each key table result will have a relative join from only one of the range of data tables, not all of them and some entries will return a null result from the data tables and we don't want this to break anything or be omitted because of the null result), and then finally join some user data to each result from the users table. This will always have a result.

Let me "draw" a basic version of my tables so you can see.

 keyTable
-----------------------------------------
|  uaID  |  userID  |  key  |  appName  |
|---------------------------------------|
|  1     |    7     | ABC01 | Physics   |
|  2     |    9     | DEF20 | Geometry  |
|  3     |    12    | XJG14 | Biology   |
|  4     |    19    | DAF09 | Chemistry |
|  5     |    27    | KYT78 | Algebra   |
|  6     |    29    | PLF43 | Statistics|
|  7     |    34    | COG89 | Geology   |
|  8     |    45    | HYL72 | Art       |
|  9     |    48    | TSK45 | History   |
|  10    |    53    | BBS94 | GeoChem   |
|  11    |    59    | DOD10 | BioChem   |
|  12    |    27    | HKV62 | Music     |
-----------------------------------------

 dataTable01
-----------------------------------------------
|  uaID  |  sector  |  subSector  |   topic   |
|---------------------------------------------|
|  2     |  circle  |  volumn     |  measure  |
|  7     | triangle | hypotenuse  |pythagoras |
|  8     | square   |             |           |
|  11    | triangle | hypotenuse  |pythagoras |
-----------------------------------------------

 dataTable02
---------------------
|  uaID  |   topic  |
|-------------------|
|  1     |   door   |
|  3     |  window  |
|  9     |  porch   |
|  12    |          |
---------------------

 dataTable03
-----------------------------------------------
|  uaID  |  sector  |  subSector  |   topic   |
|---------------------------------------------|
|  4     |   cat    |   feline    |   kitty   |
|  5     |   dog    |   canine    |   rover   |
|  6     | kangaroo |  marsupial  |   jack    |
|  10    |  bunny   |  leporidae  |   peter   |
-----------------------------------------------

 users
------------------------------------------------------------------------
|  userID  |  Title  |  firstName  |  lastName  |  email               |
|----------------------------------------------------------------------|
|    7     |   Dr    |  Melissa    |  Smith     |  [email protected]       |
|    9     |   Mr    |  Bob        |  Andrews   |  [email protected]       |
|    12    |   Miss  |  Clare      |  Greco     |  [email protected]     |
|    19    |   Mr    |  Dan        |  Fonseca   |  [email protected]       |
|    27    |   Mr    |  Matt       |  Jones     |  [email protected]      |
|    29    |   Mr    |  Chris      |  Nimmo     |  [email protected]     |
|    34    |   Mrs   |  Lisa       |  Araujo    |  [email protected]      |
|    45    |   Miss  |  Raquel     |  Bailey    |  [email protected]    |
|    48    |   Dr    |  Steven     |  Dowd      |  [email protected]    |
|    53    |   Prof  |  Roger      |  Hesp      |  [email protected]     |
|    59    |   Prof  |  Sally      |  Bryce     |  [email protected]     |
|    65    |   Mrs   |  Elena      |  Eraway    |  [email protected]     |
------------------------------------------------------------------------

And this is what I am trying to achieve as the end result:

-------------------------------------------------------------------------------------------------------------------------------
|  uaID  |  key  |  appName  |  sector  |  subSector  |   topic   |  title  |  firstName  |  lastName  |  email               | 
|-----------------------------------------------------------------------------------------------------------------------------|
|  1     | ABC01 | Physics   |          |             |   door    |   Dr    |  Melissa    |   Smith    |  [email protected]       |
|  2     | DEF20 | Geometry  |  circle  |  volumn     |  measure  |   Mr    |  Bob        |   Andrews  |  [email protected]       |
|  3     | XJG14 | Biology   |          |             |  window   |  Miss   |  Clare      |   Greco    |  [email protected]     |
|  4     | DAF09 | Chemistry |   cat    |   feline    |   kitty   |   Mr    |  Dan        |  Fonseca   |  [email protected]       |
|  5     | KYT78 | Algebra   |   dog    |   canine    |   rover   |   Mr    |  Matt       |  Jones     |  [email protected]      |
|  6     | PLF43 | Statistics| kangaroo |  marsupial  |   jack    |   Mr    |  Chris      |  Nimmo     |  [email protected]     |
|  7     | COG89 | Geology   | triangle | hypotenuse  |pythagoras |   Mrs   |  Lisa       |  Araujo    |  [email protected]      |
|  8     | HYL72 | Art       | square   |             |           |   Miss  |  Raquel     |  Bailey    |  [email protected]    |
|  9     | TSK45 | History   |          |             |   porch   |   Dr    |  Steven     |  Dowd      |  [email protected]    |
|  10    | BBS94 | GeoChem   |  bunny   |  leporidae  |   peter   |   Prof  |  Roger      |  Hesp      |  [email protected]     |
|  11    | DOD10 | BioChem   | triangle | hypotenuse  |pythagoras |   Prof  |  Sally      |  Bryce     |  [email protected]     |
|  12    | HKV62 | Music     |          |             |           |   Mr    |  Matt       |  Jones     |  [email protected]      |
-------------------------------------------------------------------------------------------------------------------------------

I am attempting to achieve this by executing:

$sql = "SELECT keyTable.uaID, keyTable.userID, keyTable.key, 
                keyTable.appName, dataTable01.sector, dataTable01.subSector, 
                dataTable01.topic, dataTable02.topic, dataTable03.sector, 
                dataTable03.subSector, dataTable03.topic, users.title, 
                users.firstName, users.lastName, users.email 
        FROM keyTable 
        LEFT OUTER JOIN dataTable01 ON keyTable.uaID = dataTable01.uaID 
        LEFT OUTER JOIN dataTable02 ON keyTable.uaID = dataTable02.uaID 
        LEFT OUTER JOIN dataTable03 ON keyTable.uaID = dataTable03.uaID 
        LEFT OUTER JOIN users ON keyTable.userID = users.userID";

I get all the keyTable data. I get all the users data right where it's supposed to join up all ok. I get all the dataTable03 data as well, but I do not get any data from dataTable01 or dataTable02 showing up in the result. If I omit the call to dataTable03 I then get all the relevant data from dataTable02 showing up, but no data from dataTable01. The call to the users table is at the end and always shows up fine. So clearly it's an issue with the matching field names in the data tables. I get no errors at all and the process completes, just with the mentioned data missing. I've tried different JOINS - INNER JOIN, OUTER JOIN, LEFT OUTER JOIN. There obviously has to be a way to achieve this but cannot seem to find any references on the web to this specific problem. Can someone tell me what I am doing incorrectly please?

Upvotes: 1

Views: 6456

Answers (2)

Barmar
Barmar

Reputation: 781078

After joining, you can use COALESCE to get the non-null value from the table with a matching row.

$sql = "SELECT k.uaID, k.userID, k.key, k.appName, 
                COALESCE(d1.sector, d3.sector, '') AS sector, 
                COALESCE(d1.subSector, d3.subSector, '') AS subSector, 
                COALESCE(d1.topic, d2.topic, d3.topic, '') AS topic,
                users.title, users.firstName, users.lastName, users.email 
        FROM keyTable AS k
        LEFT OUTER JOIN dataTable01 AS d1 ON k.uaID = d1.uaID 
        LEFT OUTER JOIN dataTable02 AS d2 ON k.uaID = d2.uaID 
        LEFT OUTER JOIN dataTable03 AS d3 ON k.uaID = d3.uaID 
        LEFT OUTER JOIN users ON k.userID = users.userID
        ORDER BY k.uaID";

Another way to merge the data from the datatablesNN tables into the same column os tp use UNION.

SELECT k.uaID, k.userID, k.key, k.appName, IFNULL(d.sector, '') AS sector, IFNULL(d.subSector, '') AS subSector, IFNULL(d.topic, '') AS topic,
        u.title, u.firstName, u.lastName, u.email
FROM keyTable AS k
LEFT OUTER JOIN (
    SELECT uaID, sector, subSector, topic
    FROM dataTable01
    UNION
    SELECT uaID, NULL, NULL, topic
    FROM datatable02
    UNION
    SELECT uaID, sector, subSector, topic
    FROM datatable03) AS d
ON k.uaID = d.uaID
LEFT JOIN users AS u ON u.userID = k.userID
ORDER BY k.uaID

DEMO

Upvotes: 2

Ruben Pirotte
Ruben Pirotte

Reputation: 386

You would have to use aliases

simular issue and solution here:

php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation

select * from common inner join (
(select link from table1)
union
(select link from table2)
) as unionT
on unionT.link = common.link

Upvotes: 0

Related Questions