SQL novice needs query help. 1

[tommyc7]

Hello. I am a SQL novice and stuck on a query. I'll describe the problem with my current table structure, but if there's a better way to set up the tables, I can still change that, too.

These aren't the real names, but the example is:

Table 1: Thing

Code (K)
Description
etc.


Table 2: CategoryMap

thingCode (K) (Key from the Thing table)
catCode (K) (The actual category - so that a Thing can have multiple categories.)

Suppose Thing contains codes A, B and C and the CategoryMap table looks like:

thingCode catCode
A X
A Y
B X
B Y
B Z
C W
C X
C Y
C Z

I want to be able to choose Thing.description for all Things that have categories X, Y and Z. So, I would want B and C, but not A, from the table above.


Thanks!
Tom

 

[r937]

select T.Code
     , T.Description
  from Thing as T
inner
  join CategoryMap as M
    on M.thingCode = T.Code     
   and M.catCode in ('X','Y','Z')
group
    by T.Code
     , T.Description
having count(*) = 3

r937.com | rudy.ca

 

[tommyc7]

Perfect! Thanks!