More efficient way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

More efficient way to do this?

Author

Topic

newcents3
Starting Member

3 Posts

Posted - 2006-08-01 : 15:51:58

I need to lookup 3 different prices, all which are in the same table. Right now the SQL query does an individual select for each of the 3 prices, as they are in a different table:

Select part_description, (select top 1 customer_price
from std_products_customers spc where spc.std_products_ID=sp.std_products_ID and spc.std_customers_ID=15) as customer_price,

(select top 1 core_pricing
from std_products_customers spc where spc.std_products_ID=sp.std_products_ID and spc.std_customers_ID=15) as core_pricing,

(select top 1 repair_pricing
from std_products_customers spc where spc.std_products_ID=sp.std_products_ID and spc.std_customers_ID=15) as repair_pricing

from std_products sp where sp.std_products_ID > 90350

Is there a more efficient way to handle this?

Thank you for any help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 16:30:35

Since I don't know your DDL, this might be wrong, If so, change LEFT JOIN to RIGHT JOIN and try again.

Select		sp.part_description,
		spc.customer_price
		spc.core_pricing
		spc.repair_pricing
from		std_products sp
left join	std_products_customers spc on spc.std_products_ID = sp.std_products_ID
where		sp.std_products_ID > 90350
		and (spc.std_customers_ID = 15 or spc.std_customers_ID is null)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources