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