Need help with a query (I want to rid myself of a subselect)
Posted: Thu Apr 16, 2009 5:13 pm
I am trying to figure out how I can eliminate the subselect in this query. I know that it can be done, but I just can't figure out how to do it. This is supposed to select all products that have a consumable_info record and either no consumable_join.fk_product_appliance or one that is not equal to '275965'
Code: Select all
SELECT
prd.uniqueId,
prd.productId,
prd.manufacturer,
prd.productTitle,
prd.sku,
cinfo.expire_weeks,
conjoin.quality_level,
conjoin.type_description,
conjoin.contained_by_appliance
FROM
product prd WITH (NOLOCK)
LEFT OUTER JOIN consumable_join conjoin WITH (NOLOCK) ON (
prd.uniqueId = conjoin.fk_product_consumable
)
-- make sure product is a consumable
INNER JOIN consumable_info cinfo WITH (NOLOCK) ON (
prd.uniqueId = cinfo.fk_product
)
WHERE
prd.uniqueId NOT IN (
SELECT
p.uniqueId
FROM
product p
LEFT OUTER JOIN consumable_join cj ON (
p.uniqueId = cj.fk_product_consumable
)
-- make sure product is a consumable
INNER JOIN consumable_info ci ON (
p.uniqueId = ci.fk_product
)
WHERE
fk_product_appliance = '275965'
)
ORDER BY
prd.manufacturer,
prd.productId