Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: Trouble with outer joins

    I am having trouble getting this query to return an entry for each supplier ID, regardless of whether information from any of the other tables exists. Can you let me know what I am doing wrong, as I am currently only getting about a thrid of the records I should be getting?

    SELECT
    supplier.supplier_name,
    product_category.DESCRIPTION WORK_GROUP,
    product.PRODUCT_NAME COMMODITY,
    decode(supplier_products.CLASSIFICATION, 'D', 'Disqualified', 'UH', 'High Risk', 'I', 'Integrated', 'P', 'Preferred', 'S', 'Strategic', 'T', 'Tactical', 'U', 'Unclassified') CLASSIFICATION,
    decode(supplier.status,'A','Active', 'I','Inactive', 'P','In Progress', 'E','Evergreen') supplier_status,
    supplier.vendor_number,
    supplier_contact.contact_name contract_contact_name,
    supplier_contact.contact_phone_no contract_contact_phone,
    supplier_contact.CONTACT_EMAIL_ADDR contract_contact_email,
    smemployees.buyer_name contract_manager,
    supplier.mwbe mwbe
    FROM supplier,
    product,
    smemployees,
    product_category,
    supplier_products,
    supplier_contact
    where supplier.supplier_id(+) = supplier_products.supplier_id
    and supplier_products.PRODUCT_ID = product.ID
    and supplier.supplier_id = supplier_contact.supplier_id
    and supplier_products.product_id = product_category.ID
    and smemployees.id = supplier.main_aws_contact_id
    ORDER BY
    supplier.Supplier_Name


    Thanks,
    -Doug Picanzi

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    tables which are outer-joined must have outer-joins (not regular) from any other tables:

    FROM supplier,
    product,
    smemployees,
    product_category,
    supplier_products,
    supplier_contact
    where supplier.supplier_id(+) = supplier_products.supplier_id
    and supplier_products.PRODUCT_ID = product.ID
    and supplier.supplier_id = supplier_contact.supplier_id(+)
    and supplier_products.product_id = product_category.ID
    and smemployees.id = supplier.main_aws_contact_id

    3rd line from the bottom - add outer-join
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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