SQL - identifying missing matches in two tables

[The Wanderer]

Hi Everyone,

I'm trying to perform a query on some responses I have stored. I want to be
able to add up a score (using SUM() ) but first checking that all the
questions have been answered.

I've tried
SELECT *
FROM `questions`
LEFT JOIN `responses`
ON `questions`.`qid` = `responses`.`qid`
AND `responses`.`yearid` = 1 AND `responses`.`uid` = 1
WHERE `value` = NULL OR '-1'
ORDER BY value DESC

but it only returns all the rows even if value is '1' those question

a entry for a question is created with a `value` value of -1 when it is
accessed.

In short, I need to be able to identify those question which either don't
have an entry in `responses` for them or have an entry set to `-1`

RESPONSES
+-------+---------+
| Field | Type |
+-------+---------+
| uid | int(11) |
| yearid | int(11) |
| qid | int(11) |
| value | int(1) |
+-------+---------+

QUESTIONS
+----------+--------------+
| Field | Type |
+----------+--------------+
| qid | int(11) |
| qtext | varchar(100) |
+----------+--------------+

This is really bugging me. I can seem to make sense of it in my head, but
not able to translate it to SQL
Hope someone can help,

Cheers,
Greg

[The Wanderer]

"The Wanderer" <no***************@spamdial.pipex.com> wrote in message
news:bg**********@newsg2.svr.pol.co.uk...

Hi Everyone,

I'm trying to perform a query on some responses I have stored. I want to be able to add up a score (using SUM() ) but first checking that all the
questions have been answered.

I've tried
SELECT *
FROM `questions`
LEFT JOIN `responses`
ON `questions`.`qid` = `responses`.`qid`
AND `responses`.`yearid` = 1 AND `responses`.`uid` = 1
WHERE `value` = NULL OR '-1'
ORDER BY value DESC

but it only returns all the rows even if value is '1' those question

a entry for a question is created with a `value` value of -1 when it is
accessed.

In short, I need to be able to identify those question which either don't
have an entry in `responses` for them or have an entry set to `-1`

RESPONSES
+-------+---------+
| Field | Type |
+-------+---------+
| uid | int(11) |
| yearid | int(11) |
| qid | int(11) |
| value | int(1) |
+-------+---------+

QUESTIONS
+----------+--------------+
| Field | Type |
+----------+--------------+
| qid | int(11) |
| qtext | varchar(100) |
+----------+--------------+

[snip]

I've come a little further with
SELECT *
FROM questions AS q
LEFT JOIN responses AS r1 ON q.qid = r1.qid
JOIN responses AS r2 ON r1.uid = 1 AND r1.uid = r2.uid AND r1.qid = r2.qid
AND r1.yearid = 1 AND r1.yearid = r2.masid
WHERE r1.value IS NULL OR r1.value = '-1'

but because of the join it only returns those values which have -1 in them,
not those with NULL.
I feel like I'm so close, maybe I just need one extra word or condition and
it'll all fall into place.

Greg

[The Wanderer]

"The Wanderer" <no***************@spamdial.pipex.com> wrote in message
news:bg**********@newsg2.svr.pol.co.uk...

Hi Everyone,

I'm trying to perform a query on some responses I have stored. I want to be able to add up a score (using SUM() ) but first checking that all the
questions have been answered.

I've tried
SELECT *
FROM `questions`
LEFT JOIN `responses`
ON `questions`.`qid` = `responses`.`qid`
AND `responses`.`yearid` = 1 AND `responses`.`uid` = 1
WHERE `value` = NULL OR '-1'
ORDER BY value DESC

but it only returns all the rows even if value is '1' those question

a entry for a question is created with a `value` value of -1 when it is
accessed.

In short, I need to be able to identify those question which either don't
have an entry in `responses` for them or have an entry set to `-1`

RESPONSES
+-------+---------+
| Field | Type |
+-------+---------+
| uid | int(11) |
| yearid | int(11) |
| qid | int(11) |
| value | int(1) |
+-------+---------+

QUESTIONS
+----------+--------------+
| Field | Type |
+----------+--------------+
| qid | int(11) |
| qtext | varchar(100) |
+----------+--------------+

[snip]

I've come a little further with
SELECT *
FROM questions AS q
LEFT JOIN responses AS r1 ON q.qid = r1.qid
JOIN responses AS r2 ON r1.uid = 1 AND r1.uid = r2.uid AND r1.qid = r2.qid
AND r1.yearid = 1 AND r1.yearid = r2.masid
WHERE r1.value IS NULL OR r1.value = '-1'

but because of the join it only returns those values which have -1 in them,
not those with NULL.
I feel like I'm so close, maybe I just need one extra word or condition and
it'll all fall into place.

Greg