7

I need help on displaying one random result in which the current user has not voted on.

Currently my database setup and the last query I have tried can be found on http://sqlfiddle.com/#!2/2f91b/1

Basically I can isolate each individual item using this query:

SELECT a.img_url, a.item_id, a.user_id, a.img_status, b.item_question, c.user_name, c.user_fbid, d.voter_id, count(d.img_id) AS totalVotes
FROM os_photos a 
LEFT JOIN os_items b ON a.item_id = b.item_id
LEFT JOIN os_users c ON a.user_id = c.user_id
LEFT JOIN os_votes d ON a.img_id = d.img_id
GROUP BY a.img_id
ORDER BY RAND()
LIMIT 1

My Problem is: With the SQL knowledge that I have, I am unable to isolate the results to show only the rows in which user #2 has not voted on. I realize the problem is when I use group by, it combines the voter_id and therefore I am unable to check if user #2 has had any input for the item.

Example:

Item #  |  voter_id
1       |      2
1       |      3
2       |      2
3       |      1
3       |      4
4       |      3
4       |      1
5       |      1
5       |      2

With the above sample set, the resulting item should be either item #3, #4 or any other items which have not been voted on.

Your help, advise and knowledge is greatly appreciated.

Michael Cheung
  • 191
  • 10

1 Answers1

2

To get the items that dont exist you need a LEFT JOIN with condition that would otherwise make a positive match, and then add a WHERE clause matching one of the resulting columns to NULL:

SELECT a.img_url, a.item_id, a.user_id, a.img_status, b.item_question, c.user_name,c.user_fbid, d.voter_id, count(d.img_id) AS totalVotes
FROM os_photos a 
LEFT JOIN os_items b ON a.item_id = b.item_id
LEFT JOIN os_users c ON a.user_id = c.user_id
LEFT JOIN os_votes d ON a.img_id = d.img_id
LEFT JOIN os_votes d2 ON a.img_id = d2.img_id AND d2.voter_id=2
WHERE d2.voter_id IS NULL
GROUP BY a.img_id
ORDER BY RAND()
LIMIT 1
poncha
  • 7,198
  • 1
  • 31
  • 36
  • 1
    I tried your code in SQLfiddle and it gave me an error. I also added a example of how the results should look in case that helps. – Michael Cheung Jul 16 '12 at 08:45
  • @MichaelCheung fixed... `WHERE` should always go before `GROUP BY`. i've pasted it in wrong line ;) sorry – poncha Jul 16 '12 at 08:47
  • Thanks, that fixed the query but it is still giving me results that it shouldn't. For example, it gives me the result of item #3 when it shouldn't because user #2 has already voted on item #3. – Michael Cheung Jul 16 '12 at 08:57
  • the user voted on **img_id=3** , how does it corellate to **item_id=3**? – poncha Jul 16 '12 at 09:05
  • whoops, I just realized my mistake, re-testing it to make sure, apologize for the mistake =D – Michael Cheung Jul 16 '12 at 09:07
  • It seems to work, thank you very much. I still do not really understand how this works, but I appreciate your help – Michael Cheung Jul 16 '12 at 09:09