I'm struggling with multiple INNER JOINs

[KULP]

I used to have 3 tables: users, polls, and choices. I used one INNER JOIN query to get all choices by a specific user ID, and then I matched the poll.id with choice.pollid. I got that to work and I was happy with it, but now I'm implementing a 4th table, votes, where I will store vote id, choice id, IP, and (if logged in), user id.

My initial query:

PHP Code:

$qry = 'SELECT p.id, p.question, p.date, p.active, c.choice, c.votecount, c.pollid, c.id
FROM polls p
INNER JOIN choices c ON c.pollid = p.id
WHERE p.userid = ' . $currentid . '
ORDER BY p.date DESC'; 


I slightly modified it but it doesn't work, and I can't find the syntax for multiple joins.

PHP Code:

SELECT p.id, p.question, p.date, p.active, c.choice, c.votecount, c.pollid, c.id, v.id
FROM polls p
INNER JOIN choices c ON c.pollid = p.id
WHERE p.userid = 1000
INNER JOIN votes v ON v.pollid = p.id
WHERE p.id = 1
ORDER BY p.date DESC 


In essence, I want to pull every vote on every poll by a specific userid. And then I'll probably store that data in multi-dimensional arrays and then reference it later (?).

[KULP]

I followed instructions (here) to use parentheses and still.. nothing. I thought about taking out the ONs but I don't know which one to remove and how it would function. Hmm...

PHP Code:

SELECT p.id, p.question, p.date, p.active, c.choice, c.votecount, c.pollid, c.id, v.id, v.ipaddress
FROM (polls p
INNER JOIN choices c WHERE c.pollid = p.id
ON p.userid = 1000) 
INNER JOIN votes v WHERE v.pollid = p.id
ON p.id = 1
ORDER BY p.date DESC 


Error:

#1064 - 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 'WHERE c.pollid = p.id ON p.userid = 1000) INNER JOIN votes v WHERE v.pollid =' at line 3

[Dormilich]

Error: WHERE is illegal in this place. ON is the WHERE of a JOIN.

I’d try

SELECT
    ...
FROM polls p
-- begin of join
INNER JOIN choices c
    ON c.pollid = p.id
INNER JOIN votes v
    ON v.pollid = p.id
-- end of join
WHERE -- now the condition applied to the compiled table
    p.userid = ? -- or whatever holds the user ID

[KULP]

Dang, I tried that before but then found something that said ON and WHERE were the same thing. I changed that and then changed the statement at the end and now it works!

Now I just have to decide, if I have a vote table, if I should take the votecount column out of the choices table and just use php to manually add up votes or if I should keep it there for easy access. Hmm...

PHP Code:

SELECT p.id, p.question, p.date, p.active, c.choice, c.votecount, c.pollid, c.id, v.id, v.ipaddress 
FROM (polls p 
INNER JOIN choices c ON c.pollid = p.id)  
INNER JOIN votes v ON v.choiceid = c.id 
WHERE p.userid = 1000
ORDER BY p.date DESC 


EDIT: Would it be bad if I had two queries in one page? I'm thinking of having one query pull the polls / choices (as I already have set up) and then another pull the votes and tally them up for me.