SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with query

    I'm using MS SQL server. I have 2 tables of interest: tblCustomerOrders and tblCustomerConfig.

    tblCustomerOrders
    ---------------------------
    CustomerNum
    PartNum


    tblCustomerConfig
    ---------------------------
    CustomerNum
    PartNum
    ConfigNum

    I need a query that returns the available ConfigNums for each CustomerNum and PartNum. There is a special rule that says:
    If the CustomerNum matches in each table, return the matching configs,
    else
    return the configs for customer 0.

    Is there a way to do this in one query?

    I can do it in 2 queries like this:

    select CO.CustomerNum, CO.PartNum, CC.ConfigNum
    from tblCustomerOrders CO
    join tblCustomerConfig CC
    on CO.CustomerNum = CC.CustomerNum and CO.PartNum = CC.PartNum

    select CO.CustomerNum, CO.PartNum, CC.ConfigNum
    from tblCustomerOrders CO
    join tblCustomerConfig CC
    on CC.CustomerNum = 0 and CO.PartNum = CC.PartNum

    I can't UNION these because I want either one or the other.
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please post the DDL for the two tables.

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select 
            CO.CustomerNum, 
            CO.PartNum, 
            coalesce(CC.ConfigNum,
                     (select confignum from tblCustomerConfig
                       where CustomerNum = 0
                         and PartNum = CO.PartNum)) as confignum
      from tblCustomerOrders CO
      left outer join tblCustomerConfig CC
      on CO.CustomerNum = CC.CustomerNum 
      and CO.PartNum = CC.PartNum


Bookmarks

Posting Permissions

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