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:23 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

How about something like this instead?

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

answered Oct 20, 2009 at 05:36 AM

RickD gravatar image

RickD
1.7k 1 1 4

That gives a 'Column 'sumMD.SumMDQuantity' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.' error but gives me a clue on how to advance.
Oct 20, 2009 at 05:40 AM graham.reeds
Add sumMD.SumMDQuantity to the Group By line and it works.
Oct 20, 2009 at 06:11 AM graham.reeds
Implemented this way it takes 2m 40s. However implemented the way I was originally trying takes 2m 11s for the same db and range.
Oct 20, 2009 at 07:02 AM graham.reeds
But the way you were originally doing this didn't manage to join to every siteid as you couldn't get the @x join working.
Oct 20, 2009 at 07:39 AM RickD
But the clue was the renaming of the outer MasterDates as MD - which enabled me to use MD.SiteID = MasterDates.SiteID in the WHERE clause.
Oct 20, 2009 at 07:50 AM graham.reeds
(comments are locked)
10|1200 characters needed characters left
SELECT
MD.SiteID as SiteID ,
DLD.Product as ProductID
FROM dbo.MasterDates MD
JOIN dbo.DLData DLD
ON MD.[ID] = DLD.DownloadID
LEFT JOIN (SELECT SiteID,
SUM(dbo.DLData.Quantity) SumMDQuantity
FROM dbo.MasterDates
JOIN dbo.DLData ON MasterDates.[ID] = dbo.DLData.DownloadID
WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
GROUP BY SiteID) as sumMD
ON sumMD.SiteID = MD.SiteID
WHERE MD.[Date] BETWEEN @From AND @To
GROUP BY MD.SiteID , DLD.Product
HAVING
SUM(DLD.Quantity) > 960 OR
SUM(DLD.Quantity) > SUM(sumMD.SumMDQuantity)
ORDER BY MD.SiteID
more ▼

answered Oct 20, 2009 at 06:12 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(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:

x473
x407

asked: Oct 20, 2009 at 05:23 AM

Seen: 973 times

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