SQL query problem?

davoutuk · #1 (**permalink**) 07-23-09, 07:23

I have a mental block on how to construct a SQL query, and I'm hoping somebody can help me.

The table is...

Code:

     Expert       Tool
        A            1
        A            2
        B            1
        B            3
        C            2
        C            4

I want to return a list of Expert values only for those experts linked to both Tool '1' and Tool '2', i.e. expert 'A'.

Any suggestions?

TIA

.... davout

pootle flump · #2 (**permalink**) 07-23-09, 07:38

Assuming (Expert,Tool) is a key

Code:

SELECT Expert 
FROM myT
WHERE Tool IN (1, 2)
GROUP BY Expert
HAVING COUNT(*) = 2

blindman · #3 (**permalink**) 07-23-09, 09:00

...or, assuming it is not:

Code:

SELECT Expert 
FROM myT
WHERE Tool IN (1, 2)
GROUP BY Expert
HAVING COUNT(distinct Tool) = 2

pootle flump · #4 (**permalink**) 07-23-09, 09:23

Depends on the RDBMS

r937 · #5 (**permalink**) 07-23-09, 09:53

Quote:

Originally Posted by pootle flump

Depends on the RDBMS

more specifically, on whether it groks standard sql and the concept of keys

the latter of which is incumbent upon you to inform it the details of

hey, that made sense when i wrote it, what about when you read it?

Internet Services

Web Development

Digital Marketing

Consumer Tech