Unmatched Query Problem 1

[grnzbra]

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)

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

 

[fredericofonseca]

By putting the where clause like that you are basically changing it to a inner join

SELECT *
FROM dbo.rtblCompanyInformation ci1
LEFT JOIN dbo.rtblCompanyInformation ci2

-- the join clause needs to have all criteria - otherwise it won't work as expected
ON ci1.fldName = ci2.fldName
and ci1.fldOfficeKey = @FromOfficeKey
AND ci2.fldOfficeKey = @ToOfficeKey

-- the above will join with the required from and to
-- but without a where clause it would give all from the left table unfiltered
-- so we add a where clause only for the left side office
WHERE ci1.fldOfficeKey = @FromOfficeKey

-- now we add the is null so we only get those that do not have a matching to the second office
and ci2.fldname is null

This could also be done with a "not exists"

SELECT *
FROM dbo.rtblCompanyInformation ci1

WHERE ci1.fldOfficeKey = @FromOfficeKey
and not exists (select 1
                from dbo.rtblCompanyInformation ci2
                where ci1.fldName = ci2.fldName
                and ci2.fldOfficeKey = @ToOfficeKey
               )

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[grnzbra]

Thank you, thank you, thank you.
This will save fifty or sixty lines of code in a number of places in the script.