I have two unrelated tables in a MYSQL database. I want to select and display data from both of them.
As an example, the tables looks like this:
I would like to display the results on my site, organised alphabetically, like this:
The ID from each table is used to refer to a photo of the cat or dog. I haven't had much luck with JOIN statements, would a UNION work in this scenario?
Any help much appreciated.
SELECT Alpha, Name, CONVERT(Cat_Id, CHAR(50)) AS Cat_Id, '' AS Dog_Id FROM Cat UNION ALL SELECT Alpha, Name, '' AS Cat_Id, CONVERT(Dog_Id, CHAR(50)) AS Dog_Id FROM Dog ORDER BY Alpha
A few comments:
Strictly speaking, if we want to show empty string for missing cat or dog IDs, then we need to cast the
Dog_Id columns to
CHAR, because all values in a column need to be the same type.
It is not necessary to wrap the
UNION query to use
ORDER BY and sort by the
Alpha column, rather we can just add
ORDER BY to the end.