ina ina - 3 months ago 12
PHP Question

MySQL Select, column with same name from multiple tables, order by another column with same name

I'm trying to select rows with a certain column name and order them by another column name.

Here's my problem, into N simplified tables:

table 1: id, username, datetime, comment


table 2: id, username, datetime, vote


.

.

.

table N: id, username, datetime, bought


I want to be able to select all rows where
username='Some Name'
, ordered by datetime -- across all N tables.

--

Incidentally, does anyone know of a good book or resource on learning and practicing advanced SQL? (Tried SQLZoo, but they don't have enough advance examples.. and then there are the too-advanced ones that I'm still stumped on)

Answer

If you're looking to sort across ALL the tables at the same time (a newer record from table 3 shows up before something from table 2, etc)

The easiest way to do this across multiple tables would be a UNION:

(SELECT id, username, datetime FROM table 1 WHERE username = 'Some Name')
UNION 
(SELECT id, username, datetime FROM table 2 WHERE username = 'Some Name')
UNION
(SELECT id, somethingelse AS username, date as datetime FROM table 3 WHERE somethingelse = 'Some Name')
....
ORDER BY datetime DESC

Note that when you use a union like this, all of your select statements should return the same number of columns and be named the same (you can use aliases for this as I showed above in table 3)