Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Question Unanswered: 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
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

Posting Permissions

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