locked
Working on Self-Join in Oracle SQL RRS feed

  • Question

  • This is for a homework project. I have spent almost an hour trying to figure out how to do this and I have finally come up short. Im not looking for someone to do this for me I would really like to understand what about my code is wrong. The question

    Write a SELECT statement that returns three columns:

    vendor_id, vnedor_name, contact_name (a concatenation of vendor_contact_first_name and vendor_contact_last_name with a s space between them

    The result set should have one row for each vendor whose contact has the same last name as another vendor's contact, and it should be sorted by vendor_contact_last_name.

    Here is the statement I have written to this point 

    SELECT DISTINCT V1.vendor_id, V1.vendor_name, V1.vendor_contact_first_name + ' ' + V1.vendor_contact_last_name As contact_name
    From VENDORS V1 JOIN VENDORS V2
      ON (V1.vendor_id <> V2.vendor_id) AND
         (V1.vendor_name = V2.vendor_name)AND
         (V1.vendor_contact_first_name = V2.vendor_contact_first_name)
    ORDER BY contact_name;

    Please help

    Friday, September 29, 2017 12:50 AM

All replies