CODEWITHSUNDEEP
sqlsql-servergroup-byhaving
Mitchel Belmer
Mitchel Belmer

Reputation: 25

SQL Non unique entries

I am trying to write a query that returns me the DateCode and ContainerName from two joined tables but only give me the records where DateCode is not unique (has multiple entries) I feel like the SQL query I've written should do exactly that but I keep getting this error:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Perhaps I'm just too green to understand why this isn't working but here is my query:

SELECT LA.DateCode, C.ContainerName 
FROM CamstarSch.A_LotAttributes LA INNER JOIN 
     CamstarSch.Container C 
     ON C.ContainerId = LA.ContainerId
WHERE LA.DateCode IN (SELECT LA.DateCode 
                      FROM CamstarSch.A_LotAttributes 
                      GROUP BY LA.DateCode, C.ContainerName 
                      HAVING COUNT(*) > 1
                    );

Upvotes: 0

Views: 86

Answers (2)

Doug Coats
Doug Coats

Reputation: 7107

This is a pretty easy one, you were so close!

SELECT LA.DateCode, C.ContainerName, COUNT(*)
FROM CamstarSch.A_LotAttributes LA
INNER JOIN CamstarSch.Container C ON C.ContainerId = LA.ContainerId
GROUP BY LA.DateCode, C.ContainerName 
HAVING COUNT(*)>1

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Problem with subquery, DateCode should be from subquery query :

WHERE LA.DateCode IN (SELECT AL.DateCode 
                      FROM CamstarSch.A_LotAttributes AS AL
                      GROUP BY AL.DateCode 
                      HAVING COUNT(*) > 1
                    );

However, simple aggregation would also work : 

SELECT LA.DateCode, C.ContainerName 
FROM CamstarSch.A_LotAttributes LA INNER JOIN 
     CamstarSch.Container C 
     ON C.ContainerId = LA.ContainerId
GROUP BY LA.DateCode, C.ContainerName 
HAVING COUNT(*) > 1;

Upvotes: 2

Related Questions