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

Question

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     |  mel@email.com       |
|    9     |   Mr    |  Bob        |  Andrews   |  bob@email.com       |
|    12    |   Miss  |  Clare      |  Greco     |  clare@email.com     |
|    19    |   Mr    |  Dan        |  Fonseca   |  dan@email.com       |
|    27    |   Mr    |  Matt       |  Jones     |  matt@email.com      |
|    29    |   Mr    |  Chris      |  Nimmo     |  chris@email.com     |
|    34    |   Mrs   |  Lisa       |  Araujo    |  lisa@email.com      |
|    45    |   Miss  |  Raquel     |  Bailey    |  raquel@email.com    |
|    48    |   Dr    |  Steven     |  Dowd      |  steven@email.com    |
|    53    |   Prof  |  Roger      |  Hesp      |  roger@email.com     |
|    59    |   Prof  |  Sally      |  Bryce     |  sally@email.com     |
|    65    |   Mrs   |  Elena      |  Eraway    |  elena@email.com     |
------------------------------------------------------------------------

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    |  mel@email.com       |
|  2     | DEF20 | Geometry  |  circle  |  volumn     |  measure  |   Mr    |  Bob        |   Andrews  |  bob@email.com       |
|  3     | XJG14 | Biology   |          |             |  window   |  Miss   |  Clare      |   Greco    |  clare@email.com     |
|  4     | DAF09 | Chemistry |   cat    |   feline    |   kitty   |   Mr    |  Dan        |  Fonseca   |  dan@email.com       |
|  5     | KYT78 | Algebra   |   dog    |   canine    |   rover   |   Mr    |  Matt       |  Jones     |  matt@email.com      |
|  6     | PLF43 | Statistics| kangaroo |  marsupial  |   jack    |   Mr    |  Chris      |  Nimmo     |  chris@email.com     |
|  7     | COG89 | Geology   | triangle | hypotenuse  |pythagoras |   Mrs   |  Lisa       |  Araujo    |  lisa@email.com      |
|  8     | HYL72 | Art       | square   |             |           |   Miss  |  Raquel     |  Bailey    |  raquel@email.com    |
|  9     | TSK45 | History   |          |             |   porch   |   Dr    |  Steven     |  Dowd      |  steven@email.com    |
|  10    | BBS94 | GeoChem   |  bunny   |  leporidae  |   peter   |   Prof  |  Roger      |  Hesp      |  roger@email.com     |
|  11    | DOD10 | BioChem   | triangle | hypotenuse  |pythagoras |   Prof  |  Sally      |  Bryce     |  sally@email.com     |
|  12    | HKV62 | Music     |          |             |           |   Mr    |  Matt       |  Jones     |  matt@email.com      |
-------------------------------------------------------------------------------------------------------------------------------

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?

Why is `porch` in the `sector` column of your desired results, when it's in the `topic` column of the input table. I also wonder why `dataTable02` is missing the `sector` and `subSector` columns, I thought you said all the datatables have the same columns. — Barmar, Jul 09 '16 at 03:30
Porch was in the wrong place because I made a mistake when "drawing" my example result table. I did sort of expect that it would be obvious that the tables would not all necessarily be the same structure/content - remember these are example tables. If they were all identical then it would be more efficient to simply use one data table and refer to it via the uaID, instead of using multiple tables. — Cassandra, Jul 09 '16 at 03:37
PS - Love the fiddle you added there Barmar. I'm going to apply your answer to my real situation tomorrow and let you know how it goes but I expect it'll work via both methods as your fiddle shows! Much appreciated. — Cassandra, Jul 09 '16 at 03:41

Barmar · Accepted Answer · 2016-07-09T03:12:44.677

2

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

edited Jul 09 '16 at 03:12

answered Jul 08 '16 at 17:48

Barmar

741,623
53
500
612

Barmar your answer suggests I should be using `dataTable01.topic AS topic, dataTable02.topic AS topic, dataTable03.topic AS topic` (topic being the same alias for each top column) but your example would result in three different topic columns - `topic_01, topic_02, topic_03` - so a little confused. By the way, thanks for the edit earlier, that made it a **lot** easier to read. Appreciated! – Cassandra Jul 08 '16 at 19:08
You can't use the same alias for all of them. The whole point is that you need to give them DIFFERENT names so you can retrieve them all. – Barmar Jul 08 '16 at 19:30
Yip that's what I figured, but I still need them all in ONE respective column each at the end. – Cassandra Jul 08 '16 at 19:36
I've changed the answer to use `COALESCE` to merge the columns. I've also shown a solution using `UNION`. – Barmar Jul 08 '16 at 19:46
Someone's data is off slightly but this stuff is close – Drew Jul 09 '16 at 00:03
@Drew I fixed a few syntax errors, added some default values, and added a sqlfiddle demo that gets the same results as he desired. – Barmar Jul 09 '16 at 03:17
I'll take a look. After I had fixed some typos it was off slightly but it could have been my data – Drew Jul 09 '16 at 03:20
It was the square/porch/bunny area and the porch/peter/pythag area. But, I chalked it up to maybe he typed it out wrong from the top of his head – Drew Jul 09 '16 at 03:25
Make sure you have `NULL`, not an empty string, in the fields that are empty in his input tables. – Barmar Jul 09 '16 at 03:26
Actually, I don't think that should matter, since there's no overlap between any of the datatables. – Barmar Jul 09 '16 at 03:27
yeah, then I was re-reading the question a few times to ascertain nulls / spaces and coalesce and the intent. But I am talking about the above and bunnies and all as it just relates to your output compared to his. So your fiddle vs his question Expected Results (no drew stuff) – Drew Jul 09 '16 at 03:27
Yeah, looks like that's a mistake in the question. `porch` is in `topic` in the input, but it's in `sector` in his exxpected results. – Barmar Jul 09 '16 at 03:29
Yes the porch has been relocated to it's appropriate position now. Sorry about that, my error. Barmar huge thanks, I used the COALESCE (top version of answer) and it functions perfectly! Greatly appreciated for both the help and the learning assistance. :) – Cassandra Jul 11 '16 at 04:28

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

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

edited May 23 '17 at 12:13

Community

1
1

answered Jul 08 '16 at 17:50

Ruben Pirotte

386
2
11

Thank you Ruben, while this kinda works it does not deliver the results as my results table above. Instead it creates an much larger results table with additional alias columns and what should all be in one col for each value (like topic) appears in several newly alised columns, breaking the desired end result. – Cassandra Jul 08 '16 at 18:46
if you need to have the columns of the datatables in the same column in the result you have too look into a union, and outerjoin on that. I was trying to simulate it and got this (which works for my data, might have to tweak it a bit too your needs): added it to my original answer – Ruben Pirotte Jul 08 '16 at 19:17

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

2 Answers2