Conditional Join?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Conditional Join?

  1. #1
    Join Date
    Mar 2006
    Posts
    2

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Do the "modems" and "accessories" tables have the same colum definitions?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    not quite unfortuntely. the one column that I'm interested in is the same - "Description", however their primary key is named differently.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136

    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
    Last edited by tamilselvan; 04-04-2006 at 03:24 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width