Query help please!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query help please!!

Author

Topic

markgmcm
Starting Member

2 Posts

Posted - 2006-02-04 : 13:15:01

I have a table that has 3 columns (ID (int identity), DogIdent (nvarchar(10)), Award (nvarchar(10))).

The table has entries like this:
1 A000015 C-BSA
2 A000015 C-BGA
3 A000015 C-BTA
4 A000015 C-BSLA
5 A000016 C-BSA
6 A000016 C-BGA
7 A000016 C-BTA

I want to query to find a DogIdent where there is an Award for C-BSA, C-BGA, C-BTA, C-BSLA for each DogIdent in the table. In the example table above the result set would be A000015 since it has all 4 awards. A00016 only has three of the four.

I thought this would have been an easy query, but it isn't.

Any help out there???!!

Thanks in advance,
Mark

Kristen
Test

22859 Posts

Posted - 2006-02-04 : 13:24:37

Hi markgmcm, Welcome to SQL Team!

Untested, but it might get you on the road to a solution:


SELECT DogIdent
FROM   MyTable
GROUP BY DogIdent
HAVING     SUM(CASE WHEN Award = 'C-BSA' THEN 1 ELSE 0 END) >= 1
       AND SUM(CASE WHEN Award = 'C-BGA' THEN 1 ELSE 0 END) >= 1
       AND SUM(CASE WHEN Award = 'C-BTA' THEN 1 ELSE 0 END) >= 1
       AND SUM(CASE WHEN Award = 'C-BSLA' THEN 1 ELSE 0 END) >= 1

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-04 : 13:35:13

Here is one way to do it:


set nocount on

create table #myTable 
	(ID int identity(1,1)
	,DogIdent nvarchar(10)
	,Award nvarchar(10))
go

insert #myTable
select 'A000015', 'C-BSA' union all
select 'A000015', 'C-BGA' union all
select 'A000015', 'C-BTA' union all
select 'A000015', 'C-BSLA' union all
select 'A000016', 'C-BSA' union all
select 'A000016', 'C-BGA' union all
select 'A000016', 'C-BTA'
go

select	b.DogIdent
	,count(distinct b.Award) AwardCount
from	(
	select 'C-BSA' Award union all
	select 'C-BGA' union all
	select 'C-BTA' union all
	select 'C-BSLA'
	) as a
join #myTable b
		on b.Award  = a.Award
group by b.DogIdent
having count(distinct b.Award) = 4

Be One with the Optimizer
TG

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-04 : 16:22:31

If dogIdent, award combination is unique:

select bsa.dogIdent
from table bsa
join table bga on bga.dogIdent = bsa.dogIdent and bga.award = 'c-bga'
join table bta on bta.dogIdent = bsa.dogIdent and bta.award = 'c-bta'
join table bsla on bsla.dogIdent = bsa.dogIdent and bsla.award = 'c-bsla'
where bsa.award = 'c-bsa'

If (dogIdent, award) is not table's unique row identifier then just add distinct in the query above.

markgmcm
Starting Member

2 Posts

Posted - 2006-02-04 : 20:32:59

Thanks SOOO much for the help. This worked great and got me over a frustrating hump. Cheers!

quote:

Originally posted by Kristen

Hi markgmcm, Welcome to SQL Team!

Untested, but it might get you on the road to a solution:


SELECT DogIdent
FROM   MyTable
GROUP BY DogIdent
HAVING     SUM(CASE WHEN Award = 'C-BSA' THEN 1 ELSE 0 END) >= 1
       AND SUM(CASE WHEN Award = 'C-BGA' THEN 1 ELSE 0 END) >= 1
       AND SUM(CASE WHEN Award = 'C-BTA' THEN 1 ELSE 0 END) >= 1
       AND SUM(CASE WHEN Award = 'C-BSLA' THEN 1 ELSE 0 END) >= 1

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 04:46:42

Simplified code of TG's


select DogIdent from
(
	select 'A000015' as DogIdent , 'C-BSA' as award union all
	select 'A000015', 'C-BGA' union all
	select 'A000015', 'C-BTA' union all
	select 'A000015', 'C-BSLA' union all
	select 'A000016', 'C-BSA' union all
	select 'A000016', 'C-BGA' union all
	select 'A000016', 'C-BTA'
) T 
	where award in('C-BSA', 'C-BGA' ,'C-BTA', 'C-BSLA')
	group by dogident having count(*)=4

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-06 : 09:47:55

Maddy, minor issue but I don't think that will work if a dog has won the same award more than once. I guess you'd need count(distinct award) = 4, no?

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 09:51:44

quote:
Originally posted by TG

Maddy, minor issue but I don't think that will work if a dog has won the same award more than once. I guess you'd need count(distinct award) = 4, no?

Be One with the Optimizer
TG

Yes it is. Nice to see you back. How was Taiwan trip?

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-06 : 10:09:02

>>Yes it is. Nice to see you back. How was Taiwan trip?
Thanks!
It was not long enough away from work and at the same time too long away from the comforts of home :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources