MySQL Select From Multiple Tables + Last row from somewhere else

Question

Hello I have these 4 tables with the structure

Table: Users [ id, username, password, bouquet_id ]
Table: Bouquets [ id, bouquet_name, stream_ids = serialized array ]
Table: Streams [ id, channel_name ]
Table: activity [ id, user_id, stream_id ]

I want to select ALL users but with their info as well from other tables + THE LAST ROW from table activity per user

For example the following query:

SELECT t1.*,t2.`bouquet_name`
FROM `users` t1,`bouquets` t2
WHERE t1.`bouquet_id ` = t2.`id`
ORDER BY t1.id DESC

Takes the data from the first 2 tables and assigned the bouquet_id to its bouquet name. Now i want to have in query the last ROW from activity table WITH it's stream name [based on stream_id]

The following query does the job i want[ PER USER]

SELECT t1.channel_name
FROM `streams` t1,`activity` t2
WHERE t2.user_id = **'%d'** AND t1.id = t2.stream_id
ORDER BY t2.id DESC
LIMIT 1

But its a kind slow since for every user in the table "users" i run 2 queries. I want the 2 queries above to be embed together as one so that i will be able to select the data from the first two tables BUT WITH the last row from table activity based on user_id.

Hope you understand me thank you

Ulrich Thomas Gabor · Accepted Answer · 2014-03-28 19:00:45Z

You should not depend on "last row" = "row with greatest id". Since MySQL provides the possibility to setup master-master replications, which then offers the possibility to assign auto_increment values in more or less arbitrary order, this assumption is not always true. An additional timestamp column would be better.

You can select the most recent activity per user for example with:

SELECT user_id, MAX(id) FROM activity GROUP BY user_id

You then have to join this into your query and join in the basic activity table to retrieve the data you actually want. You may want to replace your joins with left joins too, so you will always retrieve all users regardless if there exists rows in bouquets or activity:

SELECT t1.*, t2.`bouquet_name`, activity.*
FROM `users` t1
LEFT JOIN `bouquets` t2
 ON t1.`bouquet_id ` = t2.`id`
LEFT JOIN (
  SELECT user_id, MAX(id) AS maxid FROM activity GROUP BY user_id
) mostrecent
 ON t1.user_id = mostrecent.user_id
LEFT JOIN activity
 ON t1.user_id = activity.user_id AND activity.id = mostrecent.maxid
ORDER BY t1.id DESC

And then you can join in the stream data too like

SELECT t1.*, t2.`bouquet_name`, activity.stream_id, streams.channel_name
FROM `users` t1
LEFT JOIN `bouquets` t2
 ON t1.`bouquet_id ` = t2.`id`
LEFT JOIN (
  SELECT user_id, MAX(id) AS maxid FROM activity GROUP BY user_id
) mostrecent
 ON t1.user_id = mostrecent.user_id
LEFT JOIN activity
 ON t1.user_id = activity.user_id AND activity.id = mostrecent.maxid
LEFT JOIN streams
 ON activity.stream_id = streams.id
ORDER BY t1.id DESC

Collectives™ on Stack Overflow

MySQL Select From Multiple Tables + Last row from somewhere else

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related