x

Use a left hand value in right hand SQL comparison

Note: I originally asked this yesterday on StackOverflow. However, the high-turnover of questions means there is little chance of getting a satisfactory answer.

I have this SELECT statement:

SELECT SUM(dbo.DLData.Quantity)
    FROM dbo.MasterDates
    JOIN dbo.DLData ON MasterDates.[ID] = dbo.DLData.DownloadID
    WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
    AND dbo.MasterDates.SiteID = @X

If you plug in the @From, @To and @X it works. This is to be the right hand side of a comparison. However in the comparison the @X needs to come from the left hand side of the as the SELECT on the outside is performing test on every SiteID in the set. Without this clause the main clause works. They need to go together. Here is the full select statement with the above select left in. Eventually the two having clauses will determine the key products for a site.

--INSERT INTO SiteKeyProducts
    SELECT dbo.MasterDates.SiteID as SiteID , dbo.DLData.Product as ProductID
        FROM dbo.MasterDates
        JOIN dbo.DLData ON dbo.MasterDates.[ID] = dbo.DLData.DownloadID
        WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
        GROUP BY dbo.MasterDates.SiteID , dbo.DLData.Product
        HAVING
            SUM(dbo.DLData.Quantity) > 960 OR
            SUM(dbo.DLData.Quantity) > (SELECT SUM(dbo.DLData.Quantity)
                            FROM dbo.MasterDates
                            JOIN dbo.DLData ON MasterDates.[ID] = dbo.DLData.DownloadID
                            WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
                            AND dbo.MasterDates.SiteID = @X)
        ORDER BY dbo.MasterDates.SiteID

How do you use the outer clause on an inner statement? Hell, I don't even know if I am using the correct terminology to describe my problem.

Arrgghh - Set based logic does my head in!!

more ▼

asked Oct 20, 2009 at 05:17 AM in Default

avatar image

graham.reeds
66 4 4 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The simplest form is this:

SELECT outerMaster.SiteID as SiteID , dbo.DLData.Product as ProductID
    FROM dbo.MasterDates outerMaster
    JOIN dbo.DLData ON outerMaster.[ID] = dbo.DLData.DownloadID
    WHERE outerMaster.[Date] BETWEEN @From AND @To
    GROUP BY outerMaster.SiteID , dbo.DLData.Product
    HAVING
            SUM(dbo.DLData.Quantity) > 960 OR
            SUM(dbo.DLData.Quantity) > (SELECT SUM(dbo.DLData.Quantity)
                                          FROM dbo.MasterDates innerMaster
                                          JOIN dbo.DLData ON innerMaster.[ID] = dbo.DLData.DownloadID
                                            WHERE innerMaster.[Date] BETWEEN @From AND @To
                                              AND innerMaster.SiteID = outerMaster.SiteID)
    ORDER BY outerMaster.SiteID

However, you would probably be better doing that as a join set rather than a scalar sub-query. I'll leave you to figure that one out for yourself!

more ▼

answered Oct 20, 2009 at 06:01 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

Where are you setting the value of @X?

Are you trying to do a correlated subquery?


Edit : following on from OPs comments....

   SELECT dbo.MasterDates.SiteID as SiteID , dbo.DLData.Product as ProductID
        FROM dbo.MasterDates
        JOIN dbo.DLData ON dbo.MasterDates.[ID] = dbo.DLData.DownloadID
        JOIN
            (
            SELECT 
                dbo.MasterDates.SiteID,
                SUM(dbo.DLData.Quantity) as Qty
                FROM dbo.MasterDates
                JOIN dbo.DLData ON MasterDates.[ID] = dbo.DLData.DownloadID
                WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
                group by dbo.MasterDates.SiteID
            )inlineview
            ON  MasterDates.SiteID = inlineview.SiteID
        WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
        GROUP BY dbo.MasterDates.SiteID , dbo.DLData.Product
        HAVING
            SUM(dbo.DLData.Quantity) > 960 OR
            SUM(dbo.DLData.Quantity) > inlineview.Qty
        ORDER BY dbo.MasterDates.SiteID
more ▼

answered Oct 20, 2009 at 05:53 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x501
x457

asked: Oct 20, 2009 at 05:17 AM

Seen: 6213 times

Last Updated: Oct 20, 2009 at 05:17 AM

Copyright 2016 Redgate Software. Privacy Policy