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

Question

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 1

Alias your tables in the query, ie.

SELECT t1.id, t2.username, ... FROM table1 as t1

Answer 2

You can select same column names from different tables like:

SELECT table1.username as t1user, table2.username as t2user...

Answer 3

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)

Answer 4

You can use UNIONS

Select must be somthing like this:

(SELECT comment, date FROM table1 WHERE username='Some Name')
UNION
(SELECT vote, date FROM table2 WHERE username='Some Name')
...
UNION
(SELECT bought, date FROM tableN WHERE username='Some Name')
ORDER BY date LIMIT 100;