CODEWITHSUNDEEP
mysqlsqljoin
bendenfield
bendenfield

Reputation: 135

Join multiple tables with same column name

I have these tables in my MySQL database:

General table:

+----generalTable-----+
+---------------------+
| id | scenario | ... |
+----+----------+-----+
| 1  | facebook | ... |
| 2  | chief    | ... |
| 3  | facebook | ... |
| 4  | chief    | ... |

Facebook Table:

+----facebookTable-----+
+----------------------+
| id | expiresAt | ... |
+----+-----------+-----+
| 1  | 12345678  | ... |
| 3  | 45832458  | ... |

Chief Table:

+------chiefTable------+
+----------------------+
| id | expiresAt | ... |
+----+-----------+-----+
| 2  | 43547343  | ... |
| 4  | 23443355  | ... |

Basically, the general table holds some (obviously) general data. Based on the generalTable.scenario you can look up more details in the other two tables, which are in some columns familiar (expiresAt for example) but in others not.

My question is, how to get the joined data of generalTable and the right detailed table in just one query.

So, I would like a query like this:

SELECT id, scenario, expiresAt 
FROM generalTable
    JOIN facebookTable
        ON generalTable.id = facebookTable.id
    JOIN chiefTable
        ON generalTable.id = chiefTable.id

And an output like this:

| id | scenario | expiresAt |
+----+----------+-----------+
| 1  | facebook | 12345678  |
| 2  | chief    | 43547343  |
| 3  | facebook | 45832458  |
| 4  | chief    | 23443355  |

However, this doesn't work, because both facebookTable and chiefTable have ambiguous column name "expiresAt". For the ease of use I want to keep it that way. The result table should also only have one column "expiresAt" that is automatically filled with the right values from either facebookTable or chiefTable.

Upvotes: 11

Views: 82090

Answers (2)

mgrant
mgrant

Reputation: 1031

You might want to consider adding expiredAt to your general table, and removing it from the others, to remove duplication in the schema, and to make this particular query simpler.

If you need to stick with your current schema, you can use table aliases to resolve the name ambiguity, and use two joins and a union to create the result you are looking for:

SELECT g.id, g.scenario, f.expiresAt 
FROM generalTable g
JOIN facebookTable f
ON g.id = f.id
UNION ALL
SELECT g.id, g.scenario, c.expiresAt 
FROM generalTable g
JOIN chiefTable c
ON g.id = c.id;

The outer join approach mentioned in another answer would also solve the problem.

Upvotes: 21

Uueerdo
Uueerdo

Reputation: 15941

One way you could accomplish it is with LEFT JOIN. In the result fields you can do something like this for common fields IF(fTbl.id IS NULL, cTbl.expiresAt, fTbl.expiresAt) AS expiresAt.

Upvotes: 3

Related Questions