question

awanitim avatar image
awanitim asked

Using self join

I have a Vendors table that shows details of vendors and contact Firstname and Lastname in separate columns. I wrote a query that selects the vendor ID and VendorName and Concat the Fistname and Lastname into one column Called Name. Here is the question: I want my result to show one row for each vendor whose contact has the same first name as another vendor's contact. " In my result table i have multiple contacts with the same first name. ![alt text][1] [1]: /storage/temp/3996-sql.png
sqlservermssql
sql.png (11.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Well, you could do it by using a subquery such as: SELECT vfn.VendorContactFName FROM Vendors vfn GROUP BY vfn.VendorContactFName HAVING COUNT(*) > 1 AND COUNT(DISTINCT vfn.VendorID) > 1; That will identify all the duplicated first names. Plug that into your query's WHERE clause: SELECT V1.VendorID, V2.VendorName, CONCAT(V1.VendorContactFName, ' ', V1.VendorContactLName) AS Name FROM Vendors V1, Vendors V2 WHERE V1.VendorID = V2.VendorID AND V1.VendorContactFName IN ( SELECT vfn.VendorContactFName FROM Vendors vfn GROUP BY vfn.VendorContactFName HAVING COUNT(*) > 1 AND COUNT(DISTINCT vfn.VendorID) > 1 ) ORDER BY Name;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.