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

0

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

answered Oct 20 '09 at 05:36 AM

RickD
1.6k ● 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 '09 at 05:40 AM graham.reeds

Add sumMD.SumMDQuantity to the Group By line and it works.

Oct 20 '09 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 '09 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 '09 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 '09 at 07:50 AM graham.reeds

(comments are locked)

Answer 2

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

Use a left hand value in right hand SQL comparison

Follow this question

Related Questions