0

So I've been going at this a good few hours now with no luck, not a DB expert so thought someone may be able to help here?

Essentially I have a database consisting of questions, matching answers, users, and user groups.

I'm trying to select all questions where a user in each of the team's hasn't yet got a record in the answers table.

E.g. if a group 1 user has answered question 2, it won't be selected for any other user in that group.

Here's my attempt so far:

SELECT q.*
FROM questions q

LEFT JOIN answers a ON q.id = a.question
#LEFT OUTER JOIN answers a ON q.id = a.question

LEFT JOIN people p ON a.user = p.id 
LEFT JOIN groups g ON p.group = g.id 

WHERE
    q.category = 'food'

AND 
    g.id = (SELECT group FROM people WHERE id = 1)

AND
    a.id IS NULL;

I'm guessing it's an issue with how I'm joining the people and groups, though not entirely sure.

pemak22u
  • 1

1 Answers1

0

One solution:

SELECT *
FROM questions
WHERE id NOT IN (
    SELECT DISTINCT a.question
    FROM answers a
    JOIN people p ON a.user = p.id 
    JOIN groups g ON p.group = g.id
    WHERE g.id=1
)
Randy Slavey
  • 544
  • 4
  • 19
  • This looks about right, is it possible without using NOT IN e.g. using LEFT JOIN & IS NULL? I read somewhere that this is better option performance-wise – pemak22u Jan 25 '18 at 01:05
  • You *can*, but SQL is almost surely going to create a plan that runs the same, regardless. Also, note, I added "DISTINCT" to my query. https://explainextended.com/2009/06/16/in-vs-join-vs-exists/ – Randy Slavey Jan 25 '18 at 01:31