Is this possible?

damiantaylor · #1 (**permalink**) 03-04-11, 16:29

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?

dav1mo · #2 (**permalink**) 03-04-11, 17:10

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)

r937 · #3 (**permalink**) 03-04-11, 17:15

Code:

SELECT id 
  FROM table_a 
 WHERE value IN ( 6,7 )
GROUP
    BY id
HAVING COUNT(*) = 2

damiantaylor · #4 (**permalink**) 03-05-11, 06:04

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

r937 · #5 (**permalink**) 03-05-11, 06:11

Quote:

Originally Posted by damiantaylor

... 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

cures · #6 (**permalink**) 03-06-11, 08:29

This a great info for newbie. Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech