SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help with Complex Join Statement

    I'm working on a complex (read nightmare) SQL statement that keeps throwing up the following error:

    Not unique table/alias: 'product_lookup'

    Here's the full statement:

    $sql = "SELECT rma.incident_ID AS col1,
    contact.contact_name AS col2,
    company.company_name AS col3,
    customer_service.name AS col4,
    customer_service.email AS col5,
    incident.problem AS col6,
    incident.solution AS col7,
    rma.po AS col8,
    rma.notes AS col9,
    incident.open_date AS col10,
    rma.date_rcd AS col11,
    rma.date_closed AS col12,
    rma.tracking AS col13,
    shipping_method.method AS col14,
    shipping_company.shipper AS col15,
    rma.date_shipped AS col16,
    product_lookup.temp_product_num AS col17,
    service.work_done AS col18,
    test_status.status AS col19,
    service.new_serial_num AS col20,
    replacement_parts.part_num AS col21,
    warranty_status.warranty_status AS col22
    FROM rma, company, incident, customer_service, contact
    LEFT OUTER JOIN product_lookup ON incident.ID = product_lookup.incident_ID
    LEFT OUTER JOIN shipping_method ON rma.method_ID = shipping_method.ID
    LEFT OUTER JOIN shipping_company ON rma.shipping_ID = shipping_company.ID
    LEFT OUTER JOIN service ON rma.ID = service.rma_ID
    LEFT OUTER JOIN product_lookup ON rma.product_lookup_ID = product_lookup.ID
    LEFT OUTER JOIN warranty_status ON product_lookup.warranty_ID = warranty_status.ID
    LEFT OUTER JOIN replacement_part ON rma.part_ID = replacement_part.ID
    WHERE (rma.contact_ID = contact.ID)
    AND (rma.incident_ID = incident.ID)
    AND (contact.company_ID = company.ID)
    AND (incident.cust_ser_ID = customer_service.ID)
    AND (rma.ID = $rma)";

    So is it a misplaced comma or what?

    Thanks in Advance for any help.

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LEFT OUTER JOIN product_lookup ON incident.ID = product_lookup.incident_ID
    ...
    LEFT OUTER JOIN product_lookup ON rma.product_lookup_ID = product_lookup.ID
    I don't think you are allowed to join two tables without aliasing the second one:

    LEFT OUTER JOIN product_lookup ON incident.ID = product_lookup.incident_ID
    ...
    LEFT OUTER JOIN product_lookup AS product2 ON rma.product_lookup_ID = product2.ID

  3. #3
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its amazing what a little lunch can do for ones mental capabilities



    The problem is that product_lookup is joined more than once.


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
  •