MySQL Query not correct

Question

having an issue with my MySQL query that I can not figure out...

I'm trying to get a count of how many rows are in the table "questions" that are not in the table "answers" where the user_id matches

This is my MySQL at the moment and it returns 0 rows even though it should return 13...

SELECT COUNT(*) AS total 
FROM questions 
LEFT OUTER JOIN answers 
  ON questions.quest_id = answers.ans_question 
JOIN users 
  ON answers.ans_user = users.user_id 
WHERE answers.ans_question IS null 
  and questions.quest_level <= (SELECT user_level 
                                FROM users 
                                WHERE username = 'chris3spice') 
  and answers.ans_user = (SELECT user_id 
                          FROM users 
                          WHERE username = 'chris3spice');

This is my original query which returns how many are in questions that aren't in answers... but it doesn't take into account user_id but it does take into account the user_level so no issues there...

SELECT COUNT(*) AS total 
FROM questions 
LEFT OUTER JOIN answers 
  ON questions.quest_id = answers.ans_question 
LEFT OUTER JOIN users 
  ON answers.ans_user = users.user_id 
WHERE answers.ans_question IS null 
  and questions.quest_level <= (SELECT user_level 
                                FROM users 
                                WHERE username = 'chris3spice');

Here are my tables for reference

quest_id-----quest_text------quest_ans

1__________blah________blah

...

14_________blah________blah

.

ans_id-----ans_user------ans_quest

1__________1________1

...

14_________2________13

.

. user_id-----user_name

1_________chris3spice

2_________testing

Try breaking your query down into parts to see which is causing the problem. Just do select count(*) from questions left join answers on questions.quest_id = answers.ans_question where answers.ans_question is null and see if you get what you expect for that part. — dg99
– dg99, Commented Feb 1, 2014 at 1:22
The problem with your first query is answers.ans_user = .... When you are LEFT JOINing and looking for unmatched records, answers.ans_user will be NULL and thus false. — AgRizzo
– AgRizzo, Commented Feb 1, 2014 at 1:24
I've tried breaking it down and I can get some results... I guess its with the LEFT JOIN that is killing me... So I need to get the answers that match a user id... then from there I need to get the questions from the questions table that are not already in the answers table where the user id matches... — chris3spice
– chris3spice, Commented Feb 1, 2014 at 1:31

AgRizzo · Accepted Answer · 2014-02-01 01:29:49Z

1

Try this - I moved some of your logic around to isolate the LEFT JOIN

SELECT COUNT(*) AS total 
FROM users
JOIN questions
  ON questions.quest_level <= users.user_level
LEFT OUTER JOIN answers 
  ON questions.quest_id = answers.ans_question
    AND answers.ans_user = users.user_id
WHERE answers.ans_question IS null 
  AND users.username = 'chris3spice';

answered Feb 1, 2014 at 1:29

AgRizzo

5,2711 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chris3spice Over a year ago

I believe that did it, I did not know you could use AND to combine the joins... Thank you very much

Collectives™ on Stack Overflow

MySQL Query not correct

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related