Comparing data in the same table for non existent rows

Question

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.

Answer 1

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);