CODEWITHSUNDEEP
phpmysqlsqlmysqli
Trondro Mulligan
Trondro Mulligan

Reputation: 505

Get data from 2 tables where one table is dependent of the other

Main Table: 'posts'.
Structure:

id || text || time || user_id


Secondary table: 'likes'.
Structure:

id || post_id || time || user_id


Here, the post_id from the 2nd table can (& must) be an ID from the 1st table. I want to run a query where I will fetch all IDs from the posts table of a specific user_id and also all POST_IDs from the likes table of a specific user_id.

This is what I tried but it only gets the IDs from the posts table, nothing from the likes table is fetched:

"SELECT id FROM posts WHERE id IN (SELECT post_id FROM likes WHERE user_id=$userid) OR id IN (SELECT id FROM posts WHERE user_id=$userid)"

However, when I remove the OR statement at the end, the post_ids from the likes table are fetched:

"SELECT id FROM posts WHERE id IN (SELECT post_id FROM likes WHERE user_id=$userid)"

What am I doing wrong? Please help. Much appreciated. Thanks!

Extra:

Any way to order them in ID's descending order?

If I order them by "p.id DESC" then all post_ids from likes table appear at the bottom.

For example,
there are 7 ids in post table (1, 2, 3, 4, 5, 9, 10),
3 in likes table (6, 7, 8).

Currently it displays like this: 10, 9, 5, 4, 3, 2, 1, 6, 7, 8.

How to display it like: 10, 9, 8, 7, 6, 5, 4, 3, 2, 1? Thanks!

Upvotes: 0

Views: 125

Answers (2)

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

You can try mysql LEFT JOIN on this scenario like this way if you want to get ALL id from post table, otherwise simple INNER JOIN will do your task.

SELECT p.id,l.post_id FROM posts p 
   LEFT JOIN likes l ON p.id=l.post_id 
WHERE p.user_id=$userid OR l.user_id=$userid

Edit:

SELECT p.id FROM posts p 
LEFT JOIN likes l ON p.id=l.post_id 
WHERE p.user_id=$userid OR   
l.user_id=$userid
ORDER BY p.id DESC

Upvotes: 1

Thibault Henry
Thibault Henry

Reputation: 841

Use a JOIN

SELECT id FROM posts 
LEFT JOIN likes ON posts.id = likes.post_id 
WHERE likes.user_id = $userid

Upvotes: 1

Related Questions