How to query this? - 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

Transact-SQL (2000)

How to query this?

Author

Topic

JuzDream
Starting Member

7 Posts

Posted - 2008-08-19 : 01:30:53

Hi, I have this problem.

At my PRODUCT table as shown here:

NAME:

Amy
Bill
Cheryl
Daisy

PRODUCT_ID:
111
222
333
444

I have another table called PRODUCT_HISTORY where the PRODUCT_ID is the same as the one in PRODUCT table.

PRODUCT_HISTORY :

PRODUCT_ID:

111
111
111
222
222
222
333
333
444

STATUS:
a
b
d
a
b
c
a
d
a

The result should be like this:

NAME:

Amy
Cheryl

Bill and Daisy are not retrieved out as they have PRODUCT_ID that is not have d STATUS.

How do I retrieve all the distinct NAME where all the PRODUCT_ID of NAME has STATUS = d ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:54:58

[code]SELECT p.NAME
FROM PRODUCT p
INNER JOIN PRODUCT_HISTORY ph
ON ph.PRODUCT_ID=ph.PRODUCT_ID
GROUP BY p.NAME
HAVING SUM(CASE WHEN ph.STATUS='d' THEN 1 ELSE 0 END) >0[/code]

JuzDream
Starting Member

7 Posts

Posted - 2008-08-19 : 02:16:39

Is ph.PRODUCT_ID=ph.PRODUCT_ID or p.PRODUCT_ID=ph.PRODUCT_ID?

Anyway, the records I got is still not correct.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 02:58:13

quote:
Originally posted by JuzDream

Is ph.PRODUCT_ID=ph.PRODUCT_ID or p.PRODUCT_ID=ph.PRODUCT_ID?

Anyway, the records I got is still not correct.

its second one.
why?what was the problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 03:05:47

I'm getting exact output. see below for my attempt:-

declare @test table
(ProdID int,
Name varchar(100)
)
insert into @test
 select 111,'Amy' union all
select 222,'Bill' union all
select 333,'Cheryl' union all
select 444,'Daisy'

declare @test2 table
(
ProdID int,
Status char(1)
)

insert into @test2
select 111 ,'a' union all
select 111 ,'b' union all 
select 111,'d' union all
select 222 ,'a' union all
select 222 ,'b' union all
select 222,'c'  union all
select 333,'a' union all 
select 333,'d' union all 
select 444,'a' 


select t1.Name
from @test t1
inner join @test2 t2
on t2.ProdID=t1.ProdID
group by t1.Name
HAVING SUM(case when Status='d' then 1 else 0 end) >0

output
---------------------
Name
---------
Amy
Cheryl

JuzDream
Starting Member

7 Posts

Posted - 2008-08-19 : 03:14:45

Hmm.... Let me give u more data to my table first as the data in my previous post is has not enough data to explain.

PRODUCT

NAME:
Amy
Amy
Bill
Cheryl
Cheryl
Daisy

PRODUCT_ID:
1
2
3
4
5
6

PRODUCT_HISTORY

PRODUCT_ID:

1
1
1
2
2
3
3
3
4
4
5
5
5
6
6

STATUS:

a
b
c
a
d
a
b
c
a
d
a
b
d
a
b

The expected result upon query is:

NAME:
Cheryl

The reason why Cheryl is shown only is because it is the only record that has both PRODUCT_ID to have status d but Amy only has status d on one of it's PRODUCT_ID and not both its PRODUCT_ID.

Is it possible to query with these conditions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 03:47:33

[code]select t1.Name
from @test t1
inner join @test2 t2
on t2.ProdID=t1.ProdID
group by t1.Name
HAVING SUM(case when Status='d' then 1 else 0 end) =COUNT(DISTINCT t1.ProdID)[/code]

JuzDream
Starting Member

7 Posts

Posted - 2008-08-19 : 03:55:51

You are my life saver!! It works perfectly! Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 04:01:39

quote:
Originally posted by JuzDream

You are my life saver!! It works perfectly! Thank you.

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources