Join multiple tables with same column name

Question

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.

I have the feeling, that this is actually a very easy task, but it's been a long workday for me and you know the days when you think about a problem for so long and can't see the possibly easy solution. So, can you help me?

Also, sorry for the bad English

Answer 1

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.

Answer 2

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.