SQL, returning logic of value being in another query to field

Question

This is my query as it is now. I want to add another column to the result checking if the ItemID is equal to any value in another table column (BuildComponents.ComponentID). Eg. writing 'Y' if its there and 'N' if its not. I do not want this to affect what rows are returned. I am using MS SQL 2005. The logic would be something like this:

if(ItemID is in (select ComponentID from BuildComponents) return 'Y' else return 'N'

SELECT
'Item' AS type,
i.ItemID,
i.ItemNumber AS prodid,
i.ItemName AS 'desc',
i.SellUnitMeasure AS unit,
i.SellUnitQuantity AS quantity,
i.VATExclusiveStandardCost AS pricein,
i.BaseSellingPrice AS priceout,
v1.VATPercentageRate AS vatRateIn,
1 AS vatTypeIn,
v1.VATCode AS VATCodeIn,
v1.VATCodeDescription AS VATCodeDescriptionIn,
v2.VATPercentageRate AS vatRateOut,
2 AS vatTypeOut,
v2.VATCode AS VATCodeOut,
v2.VATCodeDescription AS VATCodeDescriptionOut,
i.IsInactive AS inactive,
s.CardRecordID AS VendID,
i.SupplierItemNumber AS VendCode,
i.VATExclusiveStandardCost AS VendInPrice,
i.ItemDescription AS ProductNote,
i.CustomField1,
i.CustomField2,
i.CustomField3,
cl1.CustomListText AS CustomField4,
cl1.CustomListName AS CustomField4Name,
cl2.CustomListText AS CustomField5,
cl2.CustomListName AS CustomField5Name,
cl3.CustomListText AS CustomField6,
cl3.CustomListName AS CustomField6Name,
'' AS QuantityOnHand,
'' AS LocationName,
i.PriceIsInclusive,
i.ItemIsStocked,
ISNULL(l1.LocationName, ISNULL(l2.LocationName, 'Default Warehouse')) AS DefaultLocation,
i.PositiveAverageCost as cost
FROM Items i
LEFT JOIN ItemLocations il ON il.ItemID = i.ItemID AND il.ItemID IS NULL
LEFT JOIN VATCodes v2 ON v2.VATCodeID = i.SellVATCodeID
LEFT JOIN VATCodes v1 ON v1.VATCodeID = i.BuyVATCodeID
LEFT JOIN Suppliers s ON s.SupplierID = i.PrimarySupplierID
LEFT JOIN CustomLists cl1 ON cl1.CustomListID = i.CustomList1ID
LEFT JOIN CustomLists cl2 ON cl2.CustomListID = i.CustomList2ID
LEFT JOIN CustomLists cl3 ON cl3.CustomListID = i.CustomList3ID
LEFT JOIN Locations l1 ON l1.LocationID = i.DefaultSellLocationID
LEFT JOIN Locations l2 ON l2.LocationID = 1

Answer 1

case when exists (select ComponentID from BuildComponents where ComponentID = i.ItemID) then 'Y' else 'N' end

Answer 2

SELECT  'Item' AS type,
        …,
        COALESCE(
        (
        SELECT  TOP 1 'Y'
        FROM    BuildComponents bc
        WHERE   ComponentID = ItemID
        ), 'N'
        )
FROM    Items i,
        …

Answer 3

CASE WHEN EXISTS(SELECT * FROM BuildComponents bc WHERE bc.ComponentID = i.ItemID) THEN 'Y' ELSE 'N' END

Should do the trick.

EXISTS is an efficient way of doing it, doesn't matter if there are multiple records in BuildComponents for the ID or not, it stops as soon as it finds the existence of the first one.

Answer 4

The way to do this is to left join to the target table, and if the column is null then it is 'N', else 'Y' with a case statement.

However, if ComponentID is not unique in BuildComponents you may get more results than you expected, so you need a distinct subquery to stop that from happening.

To your join list add

LEFT JOIN (SELECT DISTINCT ComponentID FROM BuildComponents) BC ON BC.ComponentID = i.ItemID

To your select fields, you need to add another column,

CASE WHEN BC.ComponentID IS NULL THEN 'N' ELSE 'Y' END as MyColName