CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014not-exists
Craig Zirnheld
Craig Zirnheld

Reputation: 139

Comparing data in the same table for non existent rows

I have a table that has both Company name and Contacts for their respective companies. Type column has a 0 or 1 indicating whether it is a company or person. Each row has a column with a unique contact no. The 'person' row has a column called "Company no." that links the person to the company. I'm trying to return rows that show a company without any contacts in the same table. Not sure how to even start writing this query.

Upvotes: 0

Views: 57

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

DECLARE @tbl TABLE(ContactNo INT, Name VARCHAR(100), [Type] INT,CompanyNo INT);
INSERT INTO @tbl VALUES
 (100,'ACME, Inc.',0,100)
,(200,'Bob Smith',1,100)
,(300,'John Doe',1,100)
,(400,'Widget World',0,400)
,(500,'Fishing, Inc.',0,500)
,(600,'Jane Doe',1,500);

WITH TheCompanies AS
(
    SELECT * 
    FROM @tbl AS tbl
    WHERE tbl.[Type]=0
)
SELECT *
FROM TheCompanies
WHERE NOT EXISTS(SELECT 1 FROM @tbl WHERE [Type]=1 AND CompanyNo=TheCompanies.CompanyNo);

Upvotes: 1

Related Questions