ovokuro ovokuro - 17 days ago 5
MySQL Question

How can I display data from two unrelated tables?

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:
separate tables

I would like to display the results on my site, organised alphabetically, like this:
results table

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.

Answer
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 Cat_Id or 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.

Comments