To find a solution to the problem with LEFT JOIN's IN in SQL

Question

How can you have IN -clause in ON -clause with LEFT JOIN?

I am trying to select the title of a question, tag(s) for the question and question_id for the question from my database such that question_id is 14. I expect to the tags and title of the question with question_id 14.

The output should look like

title       |         question_id        |        tag
------------|----------------------------|-----------------
A           |         14                 |        php     
A           |         14                 |        perl
A           |         14                 |        sql
A           |         14                 |        databases

The problem is actually slightly more challenging than initially.

SQL command

SELECT questions.title, questions.question_id, tags.tag
    FROM questions
    LEFT JOIN tags
    ON questions.question_id = tags.question_id IN (     // Problem here!
           SELECT question_id 
           FROM questions 
           DESC LIMIT 50 )
    WHERE questions.question_id = 14
    ORDER BY was_sent_at_time
    DESC LIMIT 50;

Thanks to Joe and Tchami in solving the first problem!

Answer 1

Your query should look like this:

SELECT questions.title, questions.question_id, tags.tag
    FROM questions
    LEFT JOIN tags
    ON questions.question_id = tags.question_id
    WHERE questions.question_id = 14
    ORDER BY was_sent_at_time
    DESC LIMIT 50;

Here's an article on how left join works.

You're missing the information on how the two tables should be joined, i.e.:

ON questions.question_id = tags.question_id

And after that you specify which question_id you want:

WHERE questions.question_id = 14

Answer 2

You haven't specified the join condition for the second table, so you are selecting every tag for every title.

Use WHERE for the row condition

How are the two tables related? Add that condition to the join. For example

SELECT questions.title, questions.question_id, tags.tag
FROM questions
LEFT JOIN tags
ON questions.question_id = tags.question_id
WHERE questions.question_id = 14
ORDER BY was_sent_at_time
DESC LIMIT 50;