Sorta complex SQL (MySQL)

[Pests]

Okay, im trying to make a voteing type thing, ill explain what it does:

Theres 3 tables in mysql, one table is a userlist with the id, name, and password of each one.

Theres a table of all the possible_choices with a id, and name.

Now, theres another table named voted with an id, choice_id, and user_id.

Now, the user will log in and vote, but i need to get out every choice out of possible_choices that the user logged in has NOT voted on. Let me explain the set up of the tables a bit more:

userlist is quite simple, just stores the user info. possible_choices is a table with every choice that they can vote on (as long as they have not voted on it before, this brings us to our next point)

voted has 3 feilds, one for the id. Now, choice_id is a CHOICE out of possible_choices, and user_id is a USER out of the userlist. If user "1" voted for choice "1" then there will be a entry in the db with "x, 1, 1" x being the id.

I hope this is clear, if it isnt, please post and tell me why. If you have another way of doing this, please post it.

For recap: Every user from userlist gets to vote on every choice in possible_choices as long as they havent voted before (stored in voted).

Also, assume the ID of the person logged in is stored in a variable.

[tomhath]

Since you are using mysql you can't use the more intuitive query with a subselect. But you can still get what you want with a LEFT JOIN that selects rows from the "left" table that don't exist in the "right" table.

I didn't test this, but it should be close enough.

select p.id, p.name
from possible_choices p
left join voted v
on v.choice_id = p.id
where v.userid = $ID
and v.choice_id is null

[Pests]

Doesnt work, i had to fix up the column names you did wrong, but it still didnt work. Returned 0 rows when it was susposed to return 2.

[Pests]

Let me give you the table layout i was useing:

userlist:

id | name | password
1 | test | test
2 | test2 | test2
3 | test3 | test3

possbile_choices:

id | name
1 | choice1
2 | choice2
3 | choice3

voted:

id | user_id | choice_id
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 2 | 3
5 | 3 | 2

I dont know if it will format correctly. Well, anyways, if you prefrom the sql on user with a id of "1" your susposed to get back choice3 (because user 1 has choice 1 and choice 2 in voted)
if you did it on user 2, you should get back choice1 (choice 2 and 3 is in voted for him). Preform it on user 3 and you will get back choice1 and choice3.

[nemonoman]

hard to do in MySQL because it lacks a subselect function.

It would take the form

SELECT *
FROM possible_choices
WHERE id
NOT IN (SELECT choiceid
FROM voted
WHERE userid=1)

Lacking that structure, it takes 2 hits.

First

SELECT choiceid
FROM voted
WHERE userid=1

Next

SELECT *
FROM possible_choices
WHERE id
NOT IN (1,2) // you'll have to construct the IN array programatically.

[Pests]

Well, thanks. I havent tested it or anything, but i will soon. I understand what its doing, but i dont under stand the

NOT IN (1,2) // you'll have to construct the IN array programatically.

you added in. What do you mean by programatically

[nemonoman]

//Get values from the first select

$query="SELECT choiceid
FROM voted
WHERE userid=1 ";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) {$inarray=$row[0];}

//create the second select using those values 

$newquery="SELECT *
FROM possible_choices
WHERE id
NOT IN (";
$newquery.=implode(",", $inarray);
$newquery.=")";

//that's what I mean by programmatically

[tomhath]

Silly mistake in the query I posted, should be this. You have to select the rows you don't want in the ON portion of the left join, then select all except those in the where clause.

select p.id, p.name
from possible_choices p
left join voted v
on v.choice_id = p.id
and v.user_id = 1
where v.id is null

[Pests]

Thanks! That works just as good as nemonoman's code but alot simpler and uses less code.