SQL statement - Help. 1

[ocindian]

SQL Gurus:
Q: There are 3 tables (A,B,C) with 'price' column (for the product) in each, but all tables need not have row for the product.
My requirement is as following:
1. I need to check if that row is present in 'A', pick that price and ignore rest of the tables
2. If row is not present in 'A', check to see if it is in 'B' and get the price (ignore price in 'C')
3. If row is not in 'A' and 'B', get the price from 'C'.
How can I achieve it single SQL statement?
I need generic SQL statement.
Thanks for your valuable time.
- Deep.

 

[JarlH]

If a product's price will be found in exactly one table, then its simply:

SELECT price FROM a WHERE product = 'whatever'
UNION
SELECT price FROM b WHERE product = 'whatever'
UNION
SELECT price FROM c WHERE product = 'whatever'

 

[PHV]

SELECT price FROM A WHERE product='whatever'
UNION
SELECT price FROM B WHERE product='whatever'
AND NOT EXISTS(SELECT * FROM A WHERE product='whatever')
UNION
SELECT price FROM C WHERE product='whatever'
AND NOT EXISTS(SELECT * FROM A WHERE product='whatever')
AND NOT EXISTS(SELECT * FROM B WHERE product='whatever')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ocindian]

Thanks JarlH and PHV for the inputs. I tested PHV's solution for 2 tables and it seems to work fine!
Thanks guys.

 

[dnoeth]

Only a single row per product?

SELECT
COALESCE(a.price, b.price, c.price)
FROM A, B, C
where a.product='whatever'
and b.product='whatever'
and c.product='whatever'

Hopefully the optimzer applies the Where-conditions before doing the Cross Join...

Another version:
select
COALESCE(
(SELECT price FROM a WHERE product = 'whatever'),
(SELECT price FROM b WHERE product = 'whatever'),
(SELECT price FROM c WHERE product = 'whatever')
)

Dieter

 

[PruSQLer]

Hey PHV,

Do you have a job or do you just answer questions on this forum?

 

[r937]

PHV answers questions in lots of forums besides just ANSI SQL



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)