Use a left hand value in right hand SQL comparison

Question

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!!

Answer 1

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!

Answer 2

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

Use a left hand value in right hand SQL comparison

Follow this question

Related Questions