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

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2005-12-12 : 04:19:18

We have a table, the structure as below: (I put a blank row in the middle to make it clear)

Product_ID Retailer Retail_Price
1001 A 1.2
1001 B 1.5
1001 C 1.4

1002 B 2.0
1002 C 2.1
1002 E 1.8
1002 F 2.0

1003 A 1.7
1003 C 1.5

Basically we use the table to compare the retail price for different retailers in terms of disfferent products. My customer asked me to give a report based on above table but only shows the retail prices which contain retailer A.

For example, on above table I need to give a report like below:

Product_ID Retailer Retail_Price
1001 A 1.2
1001 B 1.5
1001 C 1.4

1003 A 1.7
1003 C 1.5

Can anybody give some ideas on how I should write a SQL to archieve this.

Many thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 04:28:44

Select * from yourTable T where exists(Select * from yourTable where Product_id =T.Product_id and Retailer ='A')

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 04:32:34

[code]Select * from yourTable T
where exists(Select * from yourTable where ~~Retailer~~ Product_ID =~~T.Retailer~~ Product_ID and Retailer ='A')[/code]

-----------------
[KH]
Don't live your life in one day

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 04:33:15