How can I move the select statement TOP 1 out and into a join?
2014-05-16 16:08:09
SELECT
s.ItemNumber
s.ImportKey
,(
SELECT top 1 MerchandiseGroupID
FROM MerchandiseGroup mg
WHERE s.StoreDepartment = mg.Name AND c.ClientNumber = s.ClientNumber
) as MerchandiseGroupID
FROM dbo.Source s
INNER JOIN dbo.Client c on s.ClientNumber = c.ClientNumber
INNER JOIN dbo.ClientVendor cv on s.Vendor = cv.ClientVendorName
INNER JOIN dbo.TypeClientWarehouse tw on c.WarehouseCode = tw.WarehouseCode
WHERE s.ImportDate > '2014-05-15 01:00:00.000'
s.ItemNumber
s.ImportKey
,(
SELECT top 1 MerchandiseGroupID
FROM MerchandiseGroup mg
WHERE s.StoreDepartment = mg.Name AND c.ClientNumber = s.ClientNumber
) as MerchandiseGroupID
FROM dbo.Source s
INNER JOIN dbo.Client c on s.ClientNumber = c.ClientNumber
INNER JOIN dbo.ClientVendor cv on s.Vendor = cv.ClientVendorName
INNER JOIN dbo.TypeClientWarehouse tw on c.WarehouseCode = tw.WarehouseCode
WHERE s.ImportDate > '2014-05-15 01:00:00.000'
SQLkiwi 2014-05-17 05:18:57
What are you trying to achieve?
2014-05-17 15:09:58
What exactly does TOP 1 mean in this context? There is no ORDER BY, so do you just want one GroupID per client, and not care which one?
2014-05-16 16:35:44
I am not sure why you want this but here it is.
SELECT s.ItemNumber , s.ImportKey , m.MerchandiseGroupID FROM dbo.Source s INNER JOIN dbo.Client c on s.ClientNumber = c.ClientNumber INNER JOIN dbo.ClientVendor cv on s.Vendor = cv.ClientVendorName INNER JOIN dbo.TypeClientWarehouse tw on c.WarehouseCode = tw.WarehouseCode OUTER APPLY ( /* there isn't DDL so supposing a department can only have one group you can remove the TOP 1. */ SELECT top 1 MerchandiseGroupID FROM MerchandiseGroup mg WHERE s.StoreDepartment = mg.Name ) m WHERE s.ImportDate > '2014-05-15 01:00:00.000'