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