How can I move the select statement TOP 1 out and into a join?

victoryismine06 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'
SQLkiwi 2014-05-17 05:18:57
What are you trying to achieve?
Aaron Bertrand 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?
Dan Holmes 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'