I have a table containing the names of the companies with whom we do business. Each of our offices has a set of records in this table so if two offices deal with the same company, there will be two records. I need to determine what companies that appear in one office (@FromOfficeKey) that do not appear in the second office (@ToOfficeKey). When I run these two, the first one gives me 49 records, while the second one gives me only one record. There is only one matching record for the second office. I would expect the second SELECT statement to give me 49 records with only the one matching record showing up on the right hand side of the result. Why might this be behaving like an INNER JOIN? (I know that an unmatched query needs a WHERE ... IS NULL clause, but that yields no records at all - a problem seems to exist even before the IS NULL clause is added)
Code:
SELECT *
FROM dbo.rtblCompanyInformation ci1
--LEFT JOIN dbo.rtblCompanyInformation ci2
--ON ci1.fldName = ci2.fldName
WHERE ci1.fldOfficeKey = @FromOfficeKey
--AND ci2.fldOfficeKey = @ToOfficeKey
SELECT *
FROM dbo.rtblCompanyInformation ci1
LEFT JOIN dbo.rtblCompanyInformation ci2
ON ci1.fldName = ci2.fldName
WHERE ci1.fldOfficeKey = @FromOfficeKey
AND ci2.fldOfficeKey = @ToOfficeKey