Mafullah Backtrack Mafullah Backtrack - 2 months ago 7
SQL Question

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!

Answer

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.