CODEWITHSUNDEEP
sqljoinduplicates
Daniel Maher
Daniel Maher

Reputation: 43

Show duplicates from SQL join

Table 1- Job

JobID
JobCustomerID
JobAddressID

Table 2- Addresses

AddressID
AStreetAddress

Table 3- Customer

CustomerID
CustomerName

Query:

SELECT *
FROM [Jobs] 
LEFT JOIN [Addresses] ON [Jobs].JobAddressID = dbo.Addresses.AddressID  
LEFT JOIN [Customers] ON [Jobs].JobCustomerID = [Customers].CustomerID
GROUP BY AStreetAddress
HAVING (COUNT(AStreetAddress) > 1)

I am trying to find the jobs with duplicated addresses.

Error

Column Jobs.JobID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 75

Answers (3)

Backs
Backs

Reputation: 24903

With group by you can use in select only columns that are in group by or aggregate functions:

SELECT AStreetAddress
FROM [Jobs] 
LEFT JOIN [Addresses] ON [Jobs].JobAddressID = dbo.Addresses.AddressID  
LEFT JOIN [Customers] ON [Jobs].JobCustomerID = [Customers].CustomerID
GROUP BY AStreetAddress
HAVING (COUNT(AStreetAddress) > 1)

You need something like this:

SELECT [Jobs].ID
FROM [Jobs] 
LEFT JOIN [Addresses] ON [Jobs].JobAddressID = dbo.Addresses.AddressID  
LEFT JOIN [Customers] ON [Jobs].JobCustomerID = [Customers].CustomerID
GROUP BY [Jobs].ID
HAVING (COUNT(*) > 1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Duplicate addresses? First, the customer table seems very irrelevant to this question. Second, you don't need left join. If addresses are duplicated, then there are matches between the table. Third, you have two ways to get duplicates: the address id could be duplicated or the address itself.

If you want the jobs with duplicate addresses, I would expect a query like this:

SELECT j.JobId, a.AStreetAddress
FROM [Jobs] j JOIN
     [Addresses] a
     ON j.JobAddressID = a.AddressID  
GROUP BY j.JobId, a.AStreetAddress
HAVING COUNT(*) > 1;

Upvotes: 0

Michal Šuvada
Michal Šuvada

Reputation: 170

This should work for you:

SELECT *
FROM [Jobs] 

  left JOIN [Addresses] ON [Jobs].JobAddressID = dbo.Addresses.AddressID  
  left JOIN [Customers] ON [Jobs].JobCustomerID = [Customers].CustomerID

WHERE [AStreetAddress] IN (SELECT [AStreetAddress] FROM [Addresses] GROUP BY [AStreetAddress] HAVING COUNT(*) > 1)

Upvotes: 0

Related Questions