Stupid SQL Tricks

  • Article

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