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: