Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Is this possible?

    Hi all,
    I'm still pretty inexperienced with SQL so this might be a dumb or easy question, but here goes....

    Is it possible to return a value from a query when the result is based over multiple rows?

    For example, I have a table (TABLE_A) containing the following rows:
    Code:
    ID        VALUE
    1           6
    1           7
    2           6
    2           8
    3           7
    3           9
    All I know for my select is the VALUE must contain both 6 AND 7.

    So, I need a query that will return ID 1 only as that is the only one that has a value of both 6 and 7.

    Is this possible?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    select ID from table_a a
    where exists (select 1 from table_a b
                       where a.ID = b.ID
                           and b.value = 6)
       and exists (select 1 from table_a c
                       where a.ID = c.ID
                           and c.value = 7)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT id 
      FROM table_a 
     WHERE value IN ( 6,7 )
    GROUP
        BY id
    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2011
    Posts
    2

    seson museum

    Thanks guys!
    I especially like the last answer as it's easier to expand if there are 3 criteria

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by damiantaylor View Post
    ... it's easier to expand if there are 3 criteria
    and ~much~ easier if you need to do something like "has at least 3 of the following 5" ...
    Code:
    SELECT id 
      FROM table_a 
     WHERE value IN ( 6,7,8,9,37 )
    GROUP
        BY id
    HAVING COUNT(*) >= 3
    sure as heck can't do this one easily with joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2011
    Location
    Malaysia
    Posts
    1
    This a great info for newbie. Thanks

Posting Permissions

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