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 '09 at 05:17 AM in Default

graham.reeds gravatar image

graham.reeds
66 4 4 5

(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 '09 at 06:01 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Implemented as a LEFT JOIN it takes ~2m 40s. Your way (and the way I was trying originally) takes ~2m 10s.
Oct 20 '09 at 07:01 AM graham.reeds
If you've got some sample data you could put up in a ZIP somewhere, I'd be happy to try and make it zip along a bit :)
Oct 20 '09 at 07:12 AM Matt Whitfield ♦♦
..Afraid not;-)
Oct 20 '09 at 07:47 AM graham.reeds
(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 '09 at 05:53 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 44 49 76

That's what I want to know - how to get @X from the left hand side of the problem.
Oct 20 '09 at 06:08 AM graham.reeds
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x472
x402

asked: Oct 20 '09 at 05:17 AM

Seen: 2336 times

Last Updated: Oct 20 '09 at 05:17 AM