SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join Query Question...

    Hey,

    I have a poll system. Below you will see a query that works with 3 tables and grabs the title of each choice, from question #1, and counts how many times they were selected in a poll. My problem is that I cannot seem to display the title of choices in that question where there are '0' responses.

    So, the below query shows:

    Choice 1 - 10 (votes)

    Choice 2 - 5 (votes

    But if Choice 3 exists, with 0 votes, it does not show.

    tables included are:
    poll_results,poll_choices,poll_members (I stripped out the demographic data I usually use to make this more understandable)

    I am NOT joining the poll_questions table. Is that one of the problems?

    Thank you for your time. (I am spacing this out for better readability)

    Code:
    SELECT choice, count( poll_results.choice_id ) AS total_answers,poll_results.user_id as poll_taker FROM poll_choices 
    
    JOIN poll_results ON ( poll_results.choice_id = poll_choices.ID )
    
    join poll_members ON (poll_members.ID=poll_results.user_id)
    
    WHERE poll_results.question_id = '1' 
    GROUP BY poll_choices.ID 
    ORDER BY total_answers LIMIT 0 , 30

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    You need an outer join

    Code:
    select choice, 
           count(poll_results.choice_id) as total_answers
      from poll_choices 
      left outer
      join poll_results 
        on poll_results.choice_id = poll_choices.ID
       and poll_results.question_id = 1 
     group by poll_choices.ID 
     order by total_answers 
     limit 0,30

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,276
    Mentioned
    119 Post(s)
    Tagged
    1 Thread(s)
    That's because the generic join terminology by default is an inner joins, which means it only returns the rows that match. If you want to show the rows which have no results, the correct join to use would be a LEFT JOIN.

    I've redone the query for you, stripping out that members table because as it stands, it throws off the question in hand (plus the GROUP BY wouldn't work as that field wasn't in there)

    Code SQL:
    SELECT choice, COUNT(r.choice_id) AS total_answers
      FROM poll_choices c
      LEFT JOIN poll_results r ON r.choice_id = c.ID
     WHERE r.question_id = '1' 
     GROUP BY c.ID 
     ORDER BY total_answers LIMIT 0, 30
     
    GROUP BY c.ID 
    ORDER BY total_answers LIMIT 0, 30
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    First, thank you for the time. Thing is, the first one lists *every* option in the db (in table poll_choices)

    The second still doesn't show 'not sure' with '0' votes. It only shows
    options that are *in* the results table.

    This one is a bugger...

  5. #5
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hate database

  6. #6
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for that.

    Has anybody had any real world success with such a query? I am able to grab this data, just without it being one solid query. I want to conserve resources so that is why I am on a quest to find the perfect query for this. I can't see why I am struggling to do this. I have tried everything I can think of, it's strange.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    What do the tables look like? A create table would help…

  8. #8
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Table poll_results:

    ID | choice_id | question_id

    1 1 1
    2 2 1

    Table poll_choices:

    ID | choice | question_id

    1 Yes 1
    2 No 1
    3 Maybe 1

    Table poll_questions:

    ID | title

    1 Test Question

    This shows what it would look like if I create 1 question, add 3 options and users
    submit a poll response 1 time for each response (2 total votes, one for each)

    The idea is that I already have a query that can pull results that EXIST in the db, but, for some reason, I can't get this printout:

    Output:

    choice | total_votes

    1 1
    2 1
    3 0 (or null)

    Now it only shows:

    choice | total_votes

    1 1
    2 1

    I hope this helps. TY for your time.

  9. #9
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I may ask, even on another board, it's been mentioned that I should use a left join. Thing is, I get the same results. Maybe I am designing this table poorly? It's curious that the left joins produce identical output as my query when I test it. Could it be an anomaly?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jbh View Post
    Could it be an anomaly?
    probably not, but if we can't see the query, there's no way to know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query was posted in the first post. That is the actual query I use which led me to realize
    I need to modify it.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ah, you're right, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I must have designed this poorly because I cannot find a solution after days of testing. Maybe it's another type of advanced mysql query function that I should use?

  14. #14
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,276
    Mentioned
    119 Post(s)
    Tagged
    1 Thread(s)
    I'm not sure why you're not getting the results you want. Are you perhaps not handling the null values properly?

    Another version of the query which should work would be (fieldnames are based on your sample data, so change accordingly):
    Code MySQL:
    SELECT q.title, c.choice, p.result
      FROM poll_question q
      JOIN poll_choices c ON q.id = c,question_id
      LEFT JOIN (SELECT question_id
                      , choice_id
                      , COUNT(*) AS result
                   FROM poll_results
                  GROUP BY question_id
                         , choice_id) r ON q.id = r.question_id AND c.id = r.choice_id

    Did you test the query directly against the database? Just to ensure that the data you think is there actually exists? If it works there, then there's something in your code which is not handling it correctly.

    However, if it is NOT working, then break the query down into smaller chunks and see which part breaks down. In other words, try each of the following combinations and see where it breaks down. Either your data is not what you're expecting or your structure isn't what you think....
    • Just do the select between poll_questions and poll_choices to ensure that works
    • Then so a select between poll_questions and poll_results
    • Then one between poll_choices and poll_results
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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