Conditional Select from two tables

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)

Conditional Select from two tables

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2009-02-01 : 01:05:06

I have two tables with similar strucrure (below).

Table A: ProductPrices...

ProductId,
ProductPrice

Table B: CustomerPriceSpecials...

ProductId,
ProductPrice,
CustomerId

Basically I need to get all of the product prices. If a record exists in the CustomerPriceSpecials table for a given customierId and product id, then I need to retrieve the price from that table. If it doesn't exist, then I need to retireve the price from the ProductPrices Table. I think in SQL 2005, there are some set functions like INtersect that might help me out here. But I don't think these are possible in SQL 2000, and I think there may be an easier way of accomplishing this anyways. Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 01:12:39

[code]
SELECT ProductId,
ProductPrice
FROM CustomerPriceSpecials
UNION ALL
SELECT ProductId,
ProductPrice
FROM ProductPrices pp
LEFT JOIN CustomerPriceSpecials cps
ON cps.ProductId=pp.ProductId
WHERE cps.ProductId IS NULL
[/code]

akashenk
Posting Yak Master

111 Posts

Posted - 2009-02-01 : 16:17:00

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 23:24:41

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources