Need help with SQL query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ravenfrost
    New Member
    • Apr 2007
    • 9

    #1

    Need help with SQL query

    Hello, if you look at the image below you can see an example table. Lets say I want to know which owners own a lizard and a dog. Lets call the table test. Select Owner from test Where Pet... I don't how the rest would go. I'm in a stump here. Any suggestions would help. Thanks.

  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    Try this one

    [CODE=mysql]SELECT *, COUNT(*) AS pet_count FROM pets where pet in ('Dog', 'Lizard')
    GROUP BY owner
    HAVING pet_count=2[/CODE]

    Comment

    • ravenfrost
      New Member
      • Apr 2007
      • 9

      #3
      thanks a lot it worked good!

      Comment

      • ravenfrost
        New Member
        • Apr 2007
        • 9

        #4
        This actually wasn't exactly what I was looking for. The result from this would be sally, chuck & mark. The results show who has a dog OR a lizard. But, I'm really looking to see who has a dog AND a lizard, thus only returning sally Thoughts?

        Comment

        • Atli
          Recognized Expert Expert
          • Nov 2006
          • 5062

          #5
          You could do something like this:

          [code=mysql]
          SELECT t1.Owner FROM test t1
          INNER JOIN test t2
          ON t1.Owner = t2.Owner
          AND t1.Pet != t2.Pet
          WHERE t1.Pet = 'Lizzard'
          AND t2.Pet = 'Cat';
          [/code]

          Comment

          • ravenfrost
            New Member
            • Apr 2007
            • 9

            #6
            Why would you use inner join if we're only working with one table here/ Maybe there's another use to INNER JOIN that I don't know about? Thanks.

            Comment

            • Atli
              Recognized Expert Expert
              • Nov 2006
              • 5062

              #7
              Originally posted by ravenfrost
              Why would you use inner join if we're only working with one table here/ Maybe there's another use to INNER JOIN that I don't know about? Thanks.
              Normal boolean expressions can only check one row at a time so we need to group the pets for each user into a single row and then check them.
              By joning the table with itself on each duplicate value of Owner I get a result set like this:
              Code:
              +----+-------+---------+----+-------+---------+
              | ID | Owner | Pet     | ID | Owner | Pet     |
              +----+-------+---------+----+-------+---------+
              |  2 | Sally | Lizzard |  1 | Sally | Cat     |
              |  1 | Sally | Cat     |  2 | Sally | Lizzard |
              |  6 | Anna  | Cat     |  3 | Anna  | Lizzard |
              |  7 | Arnar | Dog     |  4 | Arnar | Cat     |
              |  3 | Anna  | Lizzard |  6 | Anna  | Cat     |
              |  4 | Arnar | Cat     |  7 | Arnar | Dog     |
              +----+-------+---------+----+-------+---------+
              Now we can use a simple boolean test to check whether any row has the two pets you are looking for, and we group them by Owner to avoid duplicates.

              I'm sure there are other solutions I'm overlooking, but this will get the results you wanted.

              Comment

              Working...