Join is not working as expected

[eshie003]

Hi,

I need to capture only those records from Table 1 where there is no match on Table 2 on criteria below:

TABLE 1.[Agency]=TABLE 2.[Agency] AND
TABLE 1.[Type]=TABLE 2.[Type] AND
TABLE 1.

<>=TABLE 2.[Code] 


TABLE 1		
CODE	AGENCY	TYPE
H2015HO	MCO	MA
90801	MCO	MA

TABLE 2		
CODE	AGENCY	TYPE
90801	FFS	MD
90801	MCO	MA
90802	FFS	MD
90802	MCO	MA
90801GT	FFS	MD
90801GT	MCO	MA
H2015HO	MCO	MD
H2015HO	MCO	PHD

FINAL RESULT WANTED		
CODE	AGENCY	TYPE
H2015HO	MCO	MA


Please help as I've tried a number of different queries and am not getting the correct results!  Thanks in advance...

 

[bborissov]

What is this sign <>=?


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[eshie003]

Sorry, typo. Should read TABLE 1.

<>TABLE 2.[Code]

 

[bborissov]

BTW this should (not tested though) give you the result you ask:

SELECT Table1.*
FROM Table1
LEFT JOIN Table2 ON  TABLE1.Agency = TABLE2.Agency AND
                     TABLE1.Type   = TABLE2.Type   AND
                     TABLE1.Code   = TABLE2.Code
WHERE TABLE2.Code IS NULL

And:
JOINs ALWAYS works as they should, but not ALWAYS as you you wish for



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[eshie003]

I just tested it and it works! I can't believe how I missed something as simple as this...

Thank you, thank you!