Page 1 of 1

Need help with a query (I want to rid myself of a subselect)

Posted: Thu Apr 16, 2009 5:13 pm
by Luke
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

Re: Need help with a query (I want to rid myself of a subsel

Posted: Fri Apr 17, 2009 3:33 am
by VladSun
Untested
[sql]SELECT    prd.uniqueId,    prd.productId,    prd.manufacturer,    prd.productTitle,    prd.sku,    cinfo.expire_weeks,    conjoin.quality_level,    conjoin.type_description,    conjoin.contained_by_applianceFROM    product prd WITH (NOLOCK)LEFT OUTER JOIN     consumable_join conjoin WITH (NOLOCK) ON prd.uniqueId = conjoin.fk_product_consumable   
-- make sure product is a consumableINNER JOIN     consumable_info cinfo
WITH (NOLOCK) ON prd.uniqueId = cinfo.fk_productWHERE    conjoin.fk_product_appliance != '275965'    OR    conjoin.fk_product_appliance IS NULLORDER BY    prd.manufacturer,    prd.productId[/sql]

You should use brackets in this ;)
have a consumable_info record and either no consumable_join.fk_product_appliance or one that is not equal to '275965'

Code: Select all

a and (b or c)
OR

Code: Select all

a and b or c