CODEWITHSUNDEEP
sqlsql-server-2008
MFBCTRC
MFBCTRC

Reputation: 83

How to group coloumn from current sub query?

I have a query and result bellow :

SELECT DISTINCT dbo.raTMS_TruckLoadingArm.ProductFK,

dbo.raTMS_TruckLoadingBay.BayName
FROM
dbo.raTMS_TruckLoadingArm
INNER JOIN dbo.raTMS_TruckLoadingBay ON dbo.raTMS_TruckLoadingArm.TruckLoadingBayFK = dbo.raTMS_TruckLoadingBay.PriKey
WHERE
dbo.raTMS_TruckLoadingArm.ProductFK IN (8, 9, 11, 10, 7)
ORDER BY BayName

Result :

enter image description here

I want to select from above query and group by 'BayName" where 'Bayname' is have Row count = 4 , So the result is should only "Bay1"

Thank you in advance!

Upvotes: 1

Views: 23

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If I understand correctly, you can do this with a simple aggregation with a HAVING clause:

SELECT b.BayName
FROM dbo.raTMS_TruckLoadingArm a INNER JOIN
     dbo.raTMS_TruckLoadingBay b
     ON a.TruckLoadingBayFK = b.PriKey
WHERE a.ProductFK IN (8, 9, 11, 10, 7)
GROUP BY b.BayName
HAVING COUNT(DISTINCT a.ProductFK) = 4;

Notice that the use of table aliases makes the query easier to write and to read.

Upvotes: 2

Related Questions