Sort multiple Mysql queries by common field

Question

I'm new at this and don't comprehend Mysql very well. I'm trying to retrieve ALL media uploaded by a certain user, and sort it all by the date it was uploaded. Problem is, the media is in 3 different tables. Can someone provide me with a good solution?

$sql1 = "SELECT * FROM videos WHERE user_id = $user_id";
$sql2 = "SELECT * FROM photos WHERE user_id = $user_id";
$sql3 = "SELECT * FROM audios WHERE user_id = $user_id";

SORT DESC BY $result['upload_date'];

You may find your answer here: http://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql — Anthony, Dec 03 '14 at 21:55

score 1 · Accepted Answer · answered Dec 03 '14 at 22:04

1

You want to create a JOIN. If you only want rows where there are existing relationships, you can use an inner join, set up like this:

SELECT *
FROM videos v
JOIN photos p ON p.user_id = v.user_id
JOIN audios a ON a.useR_id = p.user_id
ORDER BY upload_date DESC;

The above will select all columns, which may have some repeated things. For example I believe user_id will show up once for each table you joined, so I would narrow down your select clause.

answered Dec 03 '14 at 22:04

AdamMc331

16,492
10
71
133

1

Thanks. Good point on the dupe user_id, I'll narrow down the select statement as suggested. – user3731358 Dec 03 '14 at 22:14

Sort multiple Mysql queries by common field

1 Answers1