CODEWITHSUNDEEP
phpmysqljoin
olegb
olegb

Reputation: 19

Join 2 tables in mysql, return rows that don't relate

I have 2 tables:

table 1: questions (id, question, date)

table 2: answers (id, question_id, answer, date)

a question can have more than 1 answer, some questions don't have answers. I want to output only unanswered questions

a query like SELECT * FROMquestions,answersWHERE questions.id!=answers.question_id group by questions.id doesn't work

Can someone help a newbie like me, I'm so lost in all this mysql stuff.

Upvotes: 2

Views: 172

Answers (3)

m0g
m0g

Reputation: 969

SELECT * FROM questions,answers WHERE answers.answer = null or answers.answer = "";

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453618

SELECT id, question, date
FROM questions q 
WHERE NOT EXISTS
    (SELECT * FROM answers a 
     WHERE a.question_id = q.id)

OR

SELECT id, question, date
FROM questions q 
LEFT JOIN answers a ON a.question_id = q.id
WHERE a.id IS NULL

OR

SELECT id, question, date
FROM questions q 
WHERE q.id NOT IN 
    (SELECT question_id FROM answers WHERE question_id IS NOT NULL)
                                 /* If question_id can't be NULL this is not needed*/

Upvotes: 1

Artefacto
Artefacto

Reputation: 97835

SELECT Q.id, Q.question, Q.date
FROM questions Q LEFT JOIN answers A ON (Q.id = A.question_id)
WHERE A.id IS NULL

Upvotes: 2

Related Questions