NOT EXISTS, RAND(), and JOIN

[Q695]

I'm stumped on how to get this join to work:

Statement:

SELECT * FROM votes WHERE NOT EXISTS (`votes`.`poll_id` = `user_votes`.`vote_poll_id` AND `user_votes`.`user_id`='1') limit 1 order by rand()

 

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`votes`.`poll_id` = `user_votes`.`vote_poll_id`) limit 1 order by rand()' at line 3

 

Possibly, but doubt it:

the table is completely blank.

 

Goal:

Find all the entries that the user hasn't voted on as of yet, then submit them randomly to the user.

[Barand]

what do your tables look like?

[Q695]

I know this works:

SELECT * FROM votes, user_votes     WHERE `votes`.`poll_id` = `user_votes`.`vote_poll_id` AND `user_votes`.`user_id`='1' limit 1

 

what other errors do I have from post 1?

[Barand]

To do this you need 3 tables

 

voter : voterid | voter

item : itemid | item

votes : itemid | voterid

 

Create a cartesian join between voter and item to get all combinations then left join with votes to see which combinations are missing

[Q695]

I'm struggling with the syntax.

Why do I need a third table, when I have different displays based on 'type'?

user_votes:

vote_poll_id user_id

Vote

 

votes

  poll_id user_id type item_id yes no

[Barand]

1 You need all the items being voted on so you know which are missing

2. You need all users so you know who has not voted

3. You need which user voted for which item

[Q695]

Is there a good video tutorial on how to write the pull that I need to do, or can you tell me how to write it in syntax?

[Barand]

what do your tables look like?

All 3 of them

[Q695]

I pull the data based upon the user_votes table, which tells me what database to query, because I have a byte operator in the user_votes table tied into a switch statement.  The data displays properly.

 

The elimination tables I'm having trouble with are:

user_votes //this table

vote_poll_id user_id Vote

 

and

 

votes //eliminates options from this table

poll_id user_id type item_id yes no

 

by

`votes`.`poll_id` = `user_votes`.`vote_poll_id` AND `user_votes`.`user_id`='$user'

 

Problem:

the elimination table works fine with the data when it's matched up, but not telling me the options that aren't matching up, if there are any.

[Q695]

All 3 of them

There are 5 tables having data pulled, not 3 due to the byte/switch operator I have being used.

[Barand]

which of those 2 tables, votes or user_votes, does NOT have a poll_id until the user votes?

[Q695]

user_votes has "vote_poll_id", and votes has "poll_id".

[Barand]

What gets updated when a user votes?

[Q695]

user_votes has a new insert performed.

[Barand]

To find records in votes with no matching poll_id in user_votes use a left join.

SELECT v.*
FROM votes v
LEFT JOIN user_votes uv
    ON v.poll_id = uv.vote_poll_id
WHERE v.user_id = 1 AND uv.vote_poll_id IS NULL

[Q695]

Why can you abbreviate the table as UV, and V for future reference?

[Barand]

See "MySQL Table aliases".

 

Most of the time they are an optional convenience but in some cases they are essential.

[Q695]

One more little tweak issue:

The pull wasn't pulling the vote based upon unique users

 

SELECT v.* FROM votes v LEFT JOIN user_votes uv ON v.poll_id = uv.vote_poll_id WHERE

v.user_id != $id // the one who owns the item being voted on (working)

AND

uv.voter_id = $id // unique voter that is not the owner (stops all outputs)

AND uv.vote_poll_id IS NULL ORDER BY RAND() LIMIT 1

 

Ignore the comments, as they're just footnotes for troubleshooting help.

I renamed the column name to "voter_id", because it appeared to be used a second time in the other table.

Edited June 9, 2013 by Q695

[Q695]

All items to be voted on should be opened, but instead it eliminates the last option available.

Edited June 9, 2013 by Q695

[Barand]

"WHERE" selection conditions on a left-joined table need to be part of the join.

 

Try

SELECT v.* 
FROM votes v 
    LEFT JOIN user_votes uv 
        ON v.poll_id = uv.vote_poll_id 
        AND uv.voter_id = $id
WHERE
    v.user_id != $id
    AND uv.vote_poll_id IS NULL 
ORDER BY RAND() 
LIMIT 1