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
