Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Mandy Feeney
Mandy Feeney
17,499 Points
Posted by Mandy Feeney
Mandy Feeney
17,499 Points

Problem with writing an SQL Query

I am creating a PHP polling web application but I am having trouble with one particular SQL Query that I have rewritten over and over again but still can't get the logic of it right.

This query is to select one question from the database table which holds all the poll questions , where the currently logged in user has not answered that question before (so in other words they should not be answering a poll question more than once)

My query is currently like this:

SELECT DISTINCT p.id, p.question FROM polls p, poll_results r WHERE p.id NOT IN (SELECT DISTINCT pID FROM poll_results) AND r.uID <> (SELECT id FROM users WHERE id = 1)

The tables I have in my database are:

  • a polls table to hold all the questions.
  • a users table for the users,
  • an options table to hold all poll options and the
  • poll_results table for storing the relationship between the user, the option they chose and the question for which the option belongs to.

Any suggestions for how to write the query, or an alternate way of getting a question out of the database which the user hasn't answered before.would be appreciated.

1 Answer

Jack Choi
Jack Choi
11,420 Points
Jack Choi
Jack Choi
11,420 Points

I haven't tested this but here is my first thought:

SELECT DISTINCT p.id, p.question
FROM polls p
LEFT JOIN polls_results r
  ON p.id = r.pID AND r.uID = 12345 -- replace with the uID of the current logged in user
WHERE r.pID IS NULL;

The LEFT JOIN will match all polls with any results that the current user (12345) has responded to and leave every other row blank (NULL). Then the WHERE r.pID IS NULL part should filter out all polls that the user has responded to...it's sort of saying "only give me the ones with no matches in polls_results". The usage of the users table may be unnecessary if you already have the uID handy. Hope that helps!

Mandy Feeney
Mandy Feeney
17,499 Points
Mandy Feeney
Mandy Feeney
17,499 Points

Yeah that's exactly what I needed. Thanks for your help.