SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Querying for multiple values in same column?

    I'm having difficulties writing a query that will return records that match multiple values in the same column.

    Table example
    Code:
    tblfruit
    id      value
    1       apple
    1       orange
    1       pear
    2       apple
    2       banana
    Say that the id represents some person, so in the above case a person can have many fruits (1 to m). How could I write a query that will return only the people that have both apple and orange? Not one or the other, it must have both values

    Here is how I would write it, which is wrong (impossible where clause)

    Code:
    SELECT value 
    FROM tblfruit 
    WHERE fruit = 'apple' AND fruit = 'orange'
    Why the above doesn't work, I don't know... I'm using mysql 5.0, any help is appreciated, thanks.

  2. #2
    SitePoint Evangelist hexburner's Avatar
    Join Date
    Jan 2007
    Location
    Belgium
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, read your question wrong
    You got your fieldname wrong in the fruit = 'apple' part, that should be value.

    This should work:
    Code:
    SELECT a.value, b.value
    FROM tblfruit AS a, tblfruit AS b
    WHERE a.id = b.id
    AND a.value =  'apple'
    AND b.value =  'orange'
    FOR SALE: 1 set of morals, never used, will sell cheap

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the join approach works fine for two values, but anything more complex, especially logic involving fruits they don't have, and the join approach is doomed to failure

    here's a better approach:
    Code:
    SELECT id 
      FROM tblfruit 
     WHERE value = 'apple' 
        OR value = 'orange'
    GROUP
        BY id
    HAVING count(*) = 2
    this works fine as long as it is not possible for the person to have two apples and no oranges, which is usually taken care of by existing key constraints

    however, if multiples are permitted, then the question has changed, hasn't it? it is no longer "an apple and an orange" but "at least one apple and at least one orange" and can be done like this --
    Code:
    HAVING(COUNT DISTINCT value = 2)
    how about this: people that have both an apple and an orange but not a kumquat --
    Code:
    HAVING COUNT(
            CASE WHEN value IN ('apple','orange')
                 THEN 1 END
                ) > 1
       AND COUNT(
            CASE WHEN value = 'kumquat'
                 THEN 1 END
                ) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for the help, I had no idea it was going to be so tricky to get the results I was looking for. I'm going to use r937's first solution, since the number of parameters that need to be "ANDED" are dynamic and I can use a dynamic count to go with it.


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
  •