[thelist] SQL Server Select with row filtering problem

Steve Lewis nepolon at worlddomination.net
Tue Jun 18 22:36:01 CDT 2002


I have been juggling this query around for about 60 minutes now getting
it to execute properly and playing with the various solutions I can come
up with.  I thought it was an interesting exercise to I am sharing it
with the list, in the hopes of seeing some new solutions.

The purpose of this query is to get the list of tools and a total number
allowed with the combination of  a package and a promotion.

Related Tables
tool == canonicaly enumerates the tools
package == groups a set of tools into common sets for ease of purchase /
sale
promotion == augments a package with additional features
package_tool_assoc == enumerates tools linked to a package
promotion_tool_assoc == enumerates tools linked to a promotion

The only aspect of this query that will change from execution to
execution are the promotion_tool_assoc.promotion_id and
package_tool_assoc.package_id columns which are foreign keys to the
promotion and package tables respectively.

This is what I have so far.

SELECT a.tool_id, t.title,
    CASE
        WHEN a.number_allowed IS NULL THEN 0
        ELSE a.number_allowed
    END
    +
    CASE
        WHEN r.number_allowed IS NULL THEN 0
        ELSE r.number_allowed
    END AS num
FROM tool t
    LEFT OUTER JOIN package_tool_assoc a ON t.id = a.tool_id
    LEFT OUTER JOIN promotion_tool_assoc r ON t.id = r.tool_id
WHERE     (r.promotion_id = 5) OR (a.package_id = 3)

Problem:
I want to only return rows where 'sum' > 0 but SQL Server will not allow
me to reference num from here.  The only solutions I can think of
involve repeating the CASEd sum in the WHERE clause OR turning this
query into a derived table for a second query:

SELECT * FROM
(SELECT a.tool_id, t.title,
    CASE
        WHEN a.number_allowed IS NULL THEN 0
        ELSE a.number_allowed
    END
    +
    CASE
        WHEN r.number_allowed IS NULL THEN 0
        ELSE r.number_allowed
    END AS num
FROM tool t
    LEFT OUTER JOIN package_tool_assoc a ON t.id = a.tool_id
    LEFT OUTER JOIN promotion_tool_assoc r ON t.id = r.tool_id
WHERE     (r.promotion_id = 5) OR (a.package_id = 3)
) d
WHERE d.num > 0

Which actually has the same execution plan in SQL Server when I put it
into query analyzer.  Any other solutions?

BTW - I just thought of another way to build the case that handles this
row filtering, will play with that after I send this to compare against
later.

--Steve




More information about the thelist mailing list