Ignoring columns when grouping data

[kaht]

I'm wanting to know if something like this is possible. I'll try to explain this in simplest terms.

Assume I have a database like so:
[tt]
myTable

num type junk
--- ---- ----
1 a abc
1 a xyz
1 b abc
1 c xyz
[/tt]

Now, the query that I'd like to write would pull a list of all num such that it has all 3 types - a, b, and c, ignoring the junk column.

The only way I know to write this query would be like this:

select
   num
from
   myTable 
where
   num in (
      select num from myTable where type = 'a'
   )
   and num in (
      select num from myTable where type = 'b'
   )
   and num in (
      select num from myTable where type = 'c'
   )
)

This seems incredibly redundant to me, having to pull 3 subqueries to get all the numbers that exist as all 3 types. I've tried grouping the data with a query similar to this:

select
   num
from
   myTable 
group by
   num
having
   count(*) = 3

This will not work for 2 reasons.
1) the junk column will screw things up
2) if there are multiple instances of the same row (there can be) then it will jack up the count.

Is what I'm wanting to do possible without the subqueries, or is grouping just out of the question? Is it possible to do something like having distinct count(type) = 3 or something of that nature?

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[gmmastros]

Kaht,

If I understand correctly, you want to return the NUM column only for records that have an a, b, AND c record. If so... take a look at this query.

Declare @Temp Table(Num Int, Type VarChar(1), Junk VarChar(20))

Insert Into @Temp Values(1,    'a',     'abc')
Insert Into @Temp Values(1,    'a',     'xyz')
Insert Into @Temp Values(1,    'b',     'abc')
Insert Into @Temp Values(1,    'c',     'xyz')
Insert Into @Temp Values(2,    'd',     'xyz')
Insert Into @Temp Values(3,    'a',     'xyz')
Insert Into @Temp Values(3,    'b',     'xyz')
Insert Into @Temp Values(3,    'c',     'xyz')
Insert Into @Temp Values(4,    'b',     'xyz')
Insert Into @Temp Values(4,    'c',     'xyz')

Select Num
From   (
       Select Distinct Num, Type
       From   @Temp T
       Where  T.Type In ('a','b','c')
       ) As A
Group By Num
Having Count(*) = 3

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kaht]

George, I think that is exactly what I was looking for. Thanks

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[gmmastros]

Glad I could help. Thanks for the star.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kaht]

George, when I was implementing your solution into my code I put in what I thought were the relevant bits and it did not work. After messing around for a while, I figured out what was wrong. Going back to your example, if I remove the part I've highlighted in red, it no longer works. Any clue why?

Declare @Temp Table(Num Int, Type VarChar(1), Junk VarChar(20))

Insert Into @Temp Values(1,    'a',     'abc')
Insert Into @Temp Values(1,    'a',     'xyz')
Insert Into @Temp Values(1,    'b',     'abc')
Insert Into @Temp Values(1,    'c',     'xyz')
Insert Into @Temp Values(2,    'd',     'xyz')
Insert Into @Temp Values(3,    'a',     'xyz')
Insert Into @Temp Values(3,    'b',     'xyz')
Insert Into @Temp Values(3,    'c',     'xyz')
Insert Into @Temp Values(4,    'b',     'xyz')
Insert Into @Temp Values(4,    'c',     'xyz')

Select Num
From   (
       Select Distinct Num, Type
       From   @Temp T
       Where  T.Type In ('a','b','c')
       ) [!]As A[/!]
Group By Num
Having Count(*) = 3

It seems odd that the subquery has to have a defining variable name in order to run the outer query.

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[gmmastros]

Why? I dunno. I do know that every derived table must have an alias (which this is). I suspect it's so that the query parser doesn't get confused regarding which field to use/return.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kaht]

I see, thanks again George.

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]