SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict lveale's Avatar
    Join Date
    Jun 2001
    Location
    Dublin
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with Oracle Join

    Hi,

    here's a dilly of a pickle....

    Have 3 Oracle 8 tables (below)

    I want to return a list items from TBLSOITEMS for a particular product (easy part)....

    BUT with that list I want to return the amount *IF ANY* in the current TBLSOORDERDETAIL where the current order references (by REF_NO) a specific customer in the TBLSOORDERHEADER table. I've included a query I've tried (+and failed) but I'm only getting the order back. Can anyone help?

    TABLES

    TBLSOITEMS
    ------------
    PRODUCT_ID
    ITEM_ID
    ITEM_DESC
    SORT_ORDER
    ENABLED

    TBLSOORDERDETAIL
    -----------
    ORDER_ITEM_ID
    REF_NO
    ITEM_ID
    AMOUNT

    TBLSOORDERHEADER
    -------------------
    REF_NO
    STATUS_ID
    WEB_ID

    QUERY

    select
    i.ITEM_ID,
    i.ITEM_DESC,
    i.SORT_ORDER,
    i.ENABLED,
    d.amount,
    d.item_id
    from
    TBLSOITEMS i,
    TBLSOORDERDETAIL d,
    TBLSOORDERHEADER h
    where
    d.ref_no = h.ref_no
    and h.web_id = 'JMC2'
    and status_id = 0
    and i.product_id = 1
    and i.item_id = d.item_id(+)

    RESULTS I'd Like:
    ITEM_ID
    ITEM_DESC
    [AMT] <- if exists

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    does oracle 8 support LEFT OUTER JOIN syntax?

    i can never figure that plus sign out...


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i believe it does but the (+) syntax is much better.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    >> "the (+) syntax is much better"

    is not!

    lveale, something puzzles me about your table relationships

    you say "I want to return the amount *IF ANY* in the current TBLSOORDERDETAIL where the current order references (by REF_NO) a specific customer in the TBLSOORDERHEADER table"

    is h.web_id = 'JMC2' the specific customer?

    if this customer has no orders, which item do you want? i guess what i'm asking is where do you think the need for an outer join is coming from?

    if i understand your table relationships, TBLSOORDERDETAIL is a many-to-many intersection table, and i'm guessing the AMOUNT field is to indicate how many of a particular product on a particular order

    the part i don't understand is how you could expect "if any" to happen -- either the product is ordered on the order or it isn't, and if it is then AMOUNT should be > 0, and if it isn't, then there's no relationship from JMC2's order(s) to this product

    maybe you could show a few rows of each table...


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict lveale's Avatar
    Join Date
    Jun 2001
    Location
    Dublin
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Say I do a search for a product on a website (i.e books by Kevin Yank!) and I the current user, JMC2, happen to be logged in and have already done some searches and added some products to my basket.

    Then I do a search for products and a list is returned. Within that list, if I have already chosen a product and added it to my order, then I want the amount I have chosen to appear with the item from the product search.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select i.ITEM_ID
         , i.ITEM_DESC
         , i.SORT_ORDER
         , i.ENABLED
         , d.amount
         , d.item_id
      from TBLSOITEMS i
    left outer
      join TBLSOORDERDETAIL d
        on i.item_id = d.item_id
    left outer
      join TBLSOORDERHEADER h
        on d.ref_no = h.ref_no
       and            h.web_id = 'JMC2'
       and            h.status_id = 0
     where i.product_id = 1


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
  •