SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Sep 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need help with a SELECT query

    Thank you in advance for your help!

    ----------
    Here is my table:
    ----------

    CREATE TABLE `poll_responses` (
    `pollr_id` int(11) NOT NULL auto_increment,
    `polla_id` int(11) NOT NULL,
    `pollq_id` int(11) NOT NULL,
    `userID` int(11) NOT NULL,
    `other` text NOT NULL,
    PRIMARY KEY (`pollr_id`)
    )

    ----------
    Here is my data:
    ----------

    INSERT INTO `poll_responses` (`pollr_id`, `polla_id`, `pollq_id`, `userID`, `other`) VALUES
    (19, 12, 4, 2, ''),
    (18, 13, 4, 5, ''),
    (17, 13, 4, 107, ''),
    (16, 13, 4, 108, ''),
    (24, 10, 4, 11, ''),
    (23, 10, 4, 123, ''),
    (22, 10, 4, 19, ''),
    (20, 12, 4, 12, ''),
    (41, 24, 5, 15, ''),
    (40, 13, 4, 125, ''),
    (38, 13, 4, 1, ''),
    (42, 53, 5, 123, 'Callaway Park'),
    (43, 53, 5, 2, 'Disney Land'),
    (73, 23, 5, 125, ''),
    (72, 12, 4, 77, ''),
    (56, 36, 4, 138, ''),
    (74, 23, 5, 131, '');

    I am trying to pull the pollq_id where no row contains userID =131. What I want to get as a result is "4". Instead, what I am getting is all the rows where the userID does not equal 131. What can I do?

    ----------
    Here is my original query:
    ----------
    SELECT pollq_id FROM poll_responses WHERE userID <> 131

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by laragazza View Post
    I am trying to pull the pollq_id where no row contains userID =131.
    this is an aggregate question, and requires a GROUP BY clause, because you need to examine the set of rows for each pollq_id
    Code:
    SELECT pollq_id 
      FROM poll_responses 
    GROUP
        BY pollq_id
    HAVING COUNT( CASE WHEN userID = 131
                       THEN 'uh oh'
                       ELSE NULL END ) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is an aggregate question, and requires a GROUP BY clause, because you need to examine the set of rows for each pollq_id
    Code:
    SELECT pollq_id 
      FROM poll_responses 
    GROUP
        BY pollq_id
    HAVING COUNT( CASE WHEN userID = 131
                       THEN 'uh oh'
                       ELSE NULL END ) = 0
    I can't thank you enough for the help. That worked perfectly


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
  •