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
Published Thursday, October 20, 2005 6:59 PM by philoj
Filed under: InfoPath