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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply