Query using joins [duplicate]

Question

I have the table Vendors (VendorID PK, VendorName, VendorContactFName ,.....)

I want to find out the VendorName and VendorContactFName for those Vendors whose Contact First Name is different from The Contact First Name from any other Vendor, using joins.

I can find a way of finding the list of vendors having a contact first name equal to that of some other vendor.

select
    V1.VendorContactFName,
    V1.VendorName, 
    V2.VendorName 
from vendors v1 
join vendors v2 
    on v1.vendorContactFName = v2.vendorContactFName
where 
    v2.VendorID <>V1.vendorID 
EXCEPT
Select VendorContactFname

It runs, but I cannot come up with a way of finding those vendors whose contact name is different from that of any other contact name. I keep getting overly large results - 14756 rows - whereas the original table has 128 rows.

I have even tried taking above query and using it with an except with a table containing all other ContactFnames (and other fields in original table) but that does not work.

Any ideas?

Answer 1

You have several options. Each one can answer different scenarios.

This first query give Vendors without a similar first name:

SELECT * 
FROM @vendors v
WHERE NOT EXISTS(
    SELECT 1 FROM @vendors WHERE vendorID <> v.vendorID AND VendorContactFName = v.VendorContactFName
)
;

Output:

VendorID | VendorName | VendorContactFName
2        | Simpson    | Bart

This query output Vendors with duplicate first name:

SELECT * 
FROM @vendors v
WHERE EXISTS(
    SELECT 1 FROM @vendors WHERE vendorID <> v.vendorID AND VendorContactFName = v.VendorContactFName
)
;

Output:

VendorID | VendorName | VendorContactFName
0        | Simpson    | Lisa
1        | Simpson    | John
3        | Doe        | Lisa
4        | Doe        | John
5        | Wong       | John

This query gives the number of person with a similar first name for Vendors with duplicates:

SELECT v1.VendorName, v1.VendorContactFName, count(*)
FROM @vendors v1
INNER JOIN @vendors v2
    ON v1.vendorID <> v2.vendorID AND v1.VendorContactFName = v2.VendorContactFName
GROUP BY v1.VendorName, v1.VendorContactFName
;

Output:

VendorName | VendorContactFName | count
Simpson    | Lisa               | 1
Simpson    | John               | 2
Doe        | Lisa               | 1
Doe        | John               | 2
Wong       | John               | 2

Sample Data:

DECLARE @vendors TABLE(vendorID int identity(0, 1), VendorName varchar(100), VendorContactFName varchar(100));

INSERT INTO @vendors(VendorName, VendorContactFName) VALUES
    ('Simpson', 'Lisa')
    , ('Simpson', 'John')
    , ('Simpson', 'Bart')
    , ('Doe', 'Lisa')
    , ('Doe', 'John')
    , ('Wong', 'John')
    ;