Conditional Join?

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.

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

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

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.

Code:

---

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