How to only select rows that have not been soft deleted in a SQL Query?-CodePudding

I am trying to deduplicate my companies database of companies.

The database administrator left for a new position and has not been replaced. So I as the IT support guy have been roped into babysitting it for the time being.

I am having trouble with this query in MS Access.

SELECT dbo_Companies.PostCode, 
dbo_Companies.Phone, 
dbo_Companies.CpyID, 
dbo_Companies.Name, 
dbo_Companies.Building, 
dbo_Companies.Street, 
dbo_Companies.City, 
dbo_Companies.Deleted
FROM dbo_Companies
WHERE (((dbo_Companies.PostCode) In (SELECT [PostCode] FROM [dbo_Companies] 
As Tmp GROUP BY [PostCode],[Phone] 
HAVING Count(*)>=2  And [Phone] = [dbo_Companies].[Phone])) 
AND ((dbo_Companies.Deleted)=False))
ORDER BY dbo_Companies.PostCode, dbo_Companies.Phone;

It works mostly, the problem I am having is that it searches for records in which there are more than 2 instances of a post code and phone number, which is what I want.

However I want it to not search through any soft deleted records, it does search for soft deleted records but then filters them out of the table it produces.

PostCode	Phone	CpyID	Name	Building	Street	City	Deleted
AB12 4AX	1224404407	132931	joe bloggs	example text	street 1	Aberdeen	FALSE
AB12 4AX	1224404407	99338	joe bloggs co	example text	street 1	Aberdeen	TRUE

So I end up with loads of records which fit the criteria but only because of soft deleted records.

Is there any way to make it only match records by postcode and phone that are non soft deleted records, rather then just filtering them out of the resulting table.

Thank you to anyone in advanced.

Running the query you suggested johey, I'm still encountering the same results. It outputs some records which fit my criteria and some single ones that don't.

PostCode	Phone	CpyID	Name	Building	Street	City	Deleted
BB4 4PW	01706223999	26855	DRIVER TRETT (Driver Group Plc)	Driver House 4 St Crispin Way	Haslingden	Rossendale	FALSE
BB4 4PW	01706223999	210016	Driver Trett	Driver House 4	4 St Crispin Way	Rossendale	FALSE
BB4 7PA	01706219444	131303	Jacobson Group			Rossendale	FALSE

Jacobson Group should not be showing because the only record which matches on postcode and phone number has been soft deleted as shown below.

PostCode	Phone	CpyID	Name	Building	Street	City	Deleted
BB4 7PA	01706219444	85544	JACOBSON D& SONS LTD		Bacop Road	Rossendale	True

CodePudding user response：

Okay, then try this one:

SELECT dbo_Companies.PostCode, 
  C.Phone, 
  C.CpyID, 
  C.Name, 
  C.Building, 
  C.Street, 
  C.City, 
  C.Deleted
FROM dbo_Companies AS C
WHERE C.PostCode In (
    SELECT PostCode
    FROM [dbo_Companies] As CTMP
    WHERE CTMP.Phone = C.Phone
    AND CTMP.Deleted=False
    GROUP BY CTMP.PostCode, CTMP.Phone
    HAVING Count(*)>=2  
)
AND C.Deleted=False
ORDER BY C.PostCode, C.Phone;

However, the logic of that PostCode condition doesn't make sense to me. What are you trying to achieve with that? Why does it filter companies based on the post code count? And why is the Phone relevant in that condition?