Conditional Join?

[forkboy]

Hi,

(I've had a bit of a search, but i'm not exactly sure what to call this, so its hard to search for)

Consider this,

I have 3 tables, a table or "orders", and two seperate "product" tables

Tables -
Orders
Modems
Accessories

The orders table has a "type" column, the type changes which product table I need to join to, the order table also has a "product id" column

eg, a row in the Orders table with the type as "Modems" means I need to join to the modems table to get the details for the particular product.

Is it possible to create a single query to join against the required tables conditionally? Rather than doing it as a heap of unions?

Cheers.

[slimdave]

Do the "modems" and "accessories" tables have the same colum definitions?

[forkboy]

not quite unfortuntely. the one column that I'm interested in is the same - "Description", however their primary key is named differently.

[tamilselvan]

Is it possible to create a single query to join against the required tables conditionally? Rather than doing it as a heap of unions?

Yes. You can.

Use coalesce function.


SQL> select * from orders;

ORDER_ID ORDER_TYPE PRODUCT_ID
---------- ---------- ----------
101 MODEM 2001 ---- duplicate product id
102 ACCESSORY 2001 ---- but different type 103 MODEM 2002
104 MODEM 2003

SQL> select * from modem ;

PRODUCT_ID PRODUCT_NAME
---------- ------------------------------
2001 Modem -2001
2002 Modem -2002
2003 Modem -2003
2004 Modem -2004
2005 Modem -2005

SQL> select * from accessories;

PRODUCT_ID PRODUCT_NAME
---------- ------------------------------
2001 Accessory - 2001
2002 Accessory - 2002
2003 Accessory - 2003
2004 Accessory - 2004

SQL> get coal.sql
1 select a.order_id,
2 a.ORDER_TYPE,
3 a.PRODUCT_ID,
4 coalesce(
5 ( select product_name from modem b
6 where a.order_type = 'MODEM' and
7 b.product_id = a.product_id) ,
8 ( select product_name from accessories b
9 where a.order_type != 'MODEM' and
10 b.product_id = a.product_id)
11 ) product_name
12 from ( select ORDER_ID, ORDER_TYPE, PRODUCT_ID
13* from orders) a
SQL> /

ORDER_ID ORDER_TYPE PRODUCT_ID PRODUCT_NAME
---------- ---------- ---------- -----------------------
101 MODEM 2001 Modem -2001
102 ACCESSORY 2001 Accessory - 2001
103 MODEM 2002 Modem -2002
104 MODEM 2003 Modem -2003




Tamil