-1

I have two tables: threads and comments

Threads:

  • id
  • author_id

Comments:

  • thread_id
  • author_id

thread_id in Commets table is linked to id in Threads table.

Initially i have author_id of user, that posted some Comments in several Threads.

I want to select records from Threads, where my user posted Comments. These records should be limited and ordered by id. And my user should not be author of Threads.

Please, help. Thank you.

  • Why not use only a single table, answer me??? – Anonymous Aug 08 '14 at 06:47
  • Actually i have absolutely different tables and they are can not be in single table. "Threads" and "Comments" i used to simplify my question. –  Aug 08 '14 at 06:53

1 Answers1

2

Maybe something like this:

SET @author_id=1;
SELECT
    *
FROM
    Threads
WHERE EXISTS
(
    SELECT NULL 
    FROM Comments
    WHERE author_id=@author_id
    AND Threads.thread_id = Comments.id
)
AND NOT author_id=@author_id
ORDER BY id DESC 
LIMIT 10;
Arion
  • 31,011
  • 10
  • 70
  • 88
  • Thank you! I got, that i want from your answer. But answer is not fully correct. –  Aug 08 '14 at 07:51
  • SELECT * FROM threads WHERE EXISTS(SELECT NULL FROM comments WHERE author_id = '$user_id' AND threads.id = comments.thread_id) AND author_id != '$user_id' ORDER BY id DESC LIMIT 10 –  Aug 08 '14 at 07:56