Stupid SQL Tricks


Argh.


Classic Schroedinbug. I had an outer join between two tables to populate an InfoPath multi-select listbox. (So I needed a list of potential options with indications of which options had been selected). I created an outer join and put my parameter in the WHERE clause, like this:


SELECT dbo.NeedRequestType.NeedRequestID,


   dbo.NeedType.NeedTypeID,


   dbo.NeedType.NeedType,


   dbo.NeedCategory.NeedCategory


FROM dbo.NeedCategory LEFT OUTER JOIN dbo.NeedType


   ON dbo.NeedCategory.NeedCategoryID = dbo.NeedType.NeedCategoryID


   LEFT OUTER JOIN dbo.NeedRequestType


   ON dbo.NeedType.NeedTypeID = dbo.NeedRequestType.NeedTypeID


WHERE (dbo.NeedRequestType.NeedRequestID = 11)


   OR (dbo.NeedRequestType.NeedRequestID IS NULL)


The interesting thing is that when you don’t have much test data, this works. The WHERE clause combined with the outer join effectively selects “orphan” records out of the reference table. But as the number of “orphans” drops (by being selected), so do the number of records returned, until you only get the records that match your parameter ID (in this case, 11).


The proper syntax is:


SELECT  dbo.NeedRequestType.NeedRequestID
       ,dbo.NeedType.NeedTypeID
       ,dbo.NeedType.NeedType
       ,dbo.NeedCategory.NeedCategory

 

 FROM    dbo.NeedType
 JOIN    dbo.NeedCategory
   ON    dbo.NeedCategory.NeedCategoryID = dbo.NeedType.NeedCategoryID

 

 LEFT OUTER JOIN 
         dbo.NeedRequestType
   ON    dbo.NeedType.NeedTypeID = dbo.NeedRequestType.NeedTypeID
   AND   dbo.NeedRequestType.NeedRequestID = 11

 

Ah well, live and learn.

Philo

Skip to main content