SQL query help 1

[wmconlon]

I picked my SQL in the street, so it's kind of rudimentary. Maybe someone can point me in the right direction.

Here's a table

document characteristic
1 a
1 b
1 d
2 a
2 d
2 e
3 a
3 b

I want to SELECT the document(s) that match each of a set of characteristics.

Say I want all documents that have both characteristic a AND b: This should return documents 1 and 3

Likewise, requesting characteristics a AND d returns documents 1 and 2

Say I request characteristics b AND d: I should get no rows.

thanks

 

[swampBoogie]

select document
from t
 where characteristic in ('a','d')
 group by document
 having count(*) = 2 -- this corresonds to the number 
                     -- of items in the in clause

 

[sleipnir214]

wmconlon:
Your third example is incorrect. Searching for documents which characteristics "a" and "d" should return document 1.

swampBoogie:
That's clever. A star for you...


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[wmconlon]

swampBoogie:
merci. works great.

sleipner214:
correct. I mistyped case 3, but fortunately the -esp switch worked.