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

avatar image

graham.reeds
66 4 4 8

(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

avatar image

RickD
1.7k 2 3 6

(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

avatar image

Madhivanan
1.1k 2 5 9

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

x502
x457

asked: Oct 20, 2009 at 05:23 AM

Seen: 1436 times

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

Copyright 2016 Redgate Software. Privacy Policy