从2个表中获取数据，其中一个表依赖于另一个表

Question

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!

Answer 1

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