Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts

    I'm struggling with multiple INNER JOINs

    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.idp.questionp.datep.activec.choicec.votecountc.pollidc.idv.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 (?).

  • #2
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    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.idp.questionp.datep.activec.choicec.votecountc.pollidc.idv.idv.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:
    Code:
    #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
    Last edited by KULP; 12-07-2012 at 03:27 AM.

  • #3
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,280
    Thanks
    12
    Thanked 343 Times in 339 Posts
    Error: WHERE is illegal in this place. ON is the WHERE of a JOIN.

    I’d try
    Code:
    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
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #4
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    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.idp.questionp.datep.activec.choicec.votecountc.pollidc.idv.idv.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.
    Last edited by KULP; 12-07-2012 at 02:42 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •