MySQL loop or nested query

Question

Say I have a table friends

user_1  |  user_2

Then, say I want to see what these friends have been up to, so I check the posts table and updates table

post_id  | post_title | post_message |  user_id | timestamp

update_id | title | userid | timestamp

I want to achieve logic like the following : Get list of friends, go through this list of generated friends to see if they have added anything, sorted by the most recent activities first (timestamp).

Select user_1 from friends where user_2 = '$my_userid'
union
Select user_2 from friends where user_1 = '$my_userid'

//now loop through each of these friends and check the update/post tables to see if they have added anything recently

Can I do this all through 1 MySQL query, or do I have to break it up and use PHP?

@AshwinMukhija first I generated a list of friends. Then in PHP I looped through them and did individual queries for their activities, but since it's not selecting them all at once, the result only put the events in order (timestamp) per user, not overall. — user1852176
– user1852176, Commented Feb 2, 2013 at 19:30

Techie · Accepted Answer · 2013-02-02 19:53:09Z

1

post_id | post_title | post_message | user_id | timestamp - Table_A

update_id | title | userid | timestamp - Table_B

Try the below query

SELECT A.*, B.* FROM Table_A AS A JOIN Table_B AS B ON B.userid = A.user_id
WHERE A.user_id IN (100,101,102) 
ORDER BY B.timestamp DESC;

A.* - will get you all the columns from the table A. If you want specific column A.post_id.

You can number of results by adding a LIMIT clause.

Note : I assume 100,101,102 are the user ids generated. You can add the user ids within the IN Clause.

Update

Put the user ids you need in the IN clause if you want to limit the friends. if not remove the where clause, so you can get the updates of all the friends without a limitation. You can order by any column you need. You can do ORDER BY A.user_id , A.post_id, Even multiple columns are possible

edited Feb 2, 2013 at 19:53

answered Feb 2, 2013 at 19:35

Techie

45.2k45 gold badges164 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user1852176 Over a year ago

So I can just change the IN clause to be a nested query for a list of my friends?

Techie Over a year ago

that depends on your requirement. put the user ids you need in the IN clause if you want to limit the friends. if not remove the where clause, so you can get the updates of all the friends without a limitation.

Techie Over a year ago

Yes you can write a sub query in the IN clause

user1852176 Over a year ago

how does this treat ordering (timestamp) of values in table A since it's ordered by table B?

Techie Over a year ago

You can order by any column you need. You can do ORDER BY A.user_id , A.post_id, Even multiple columns are possible

|

Collectives™ on Stack Overflow

MySQL loop or nested query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related