Linking - Strange happenings

[pjmdesigns]

Hi All,

This is a really simple "back to basics" question as the results I am getting at the moment are not in tune with results I expect.

Basically, 2 tables with an EQUAL join on LOSSID, tables may look something like:

TABLE 1
LOSSID WHATEVER
1001 a
1002 b
1003 b

TABLE 2
LOSSID CONTACT KEY
1001 <null>
1001 J. Winters
1001 <null>
1001 P. Andrews
1002 <null>
1003 P. May
1003 J. May

You get the picture!

Anyway with an EQUAL join on the above ((Table 1)EQUAL(Table 2)) by LOSSID would I be correct in expecting the following result?

LOSSID CONTACTKEY
1001 <null>
1002 <null>
1003 P. May

Unless I used a LEFT OUTER where I would then expect all records from (TABLE 1) and ALL matching records from (TABLE 2)? Is this right or have I been shown wrong in the past? That is assuming anyone actually understands what I'm going on about!

Using V8 by the way!
Much Appreciated
pjm

 

[pgtek]

hi
your first expectation is right.it will get the firat record in you table is the first is null thats the one you will get if the firs is name you get name
when you have a equal joint you get the exact matching result from table1 and table2
if you have left outher join
you get the exact matching result set return plus what ever on the table2 is return that are not equal to the table1 and if you have LOSSID CONTACT KEY
1001 <null>
1001 J. Winters
1001 <null>
1001 P. Andrews
1002 <null>
1003 P. May
1003 J. May

 

[lbass]

I use equal joins when I only want to include records where there is a presence in both tables, but this does not mean that if there is a one-to-many relationship (as in your example) that only one record will show up from the lookup table. In your example, if you are linking on LOSSID and have an equal link, for LOSSID 1002 there would be no corresponding record in the lookup table (Table 2), and since it doesn't meet the criterion of presence in both tables, it would not appear in your report at all (and therefore, the report would be unable to show a null for {Contact Key}. But there would be multiple results per LOSSID if there were more than one Contact Key, as in the cases of LOSSID 1001 and 1003. The records that show up would be:

LOSSID CONTACT KEY
1001 J. Winters
1001 P. Andrews
1003 P. May
1003 J. May

In other words, an equal join would pull records that have a presence in both tables and in a one-to-many relationship, there can be multiple presences in the second table. I agree with Pgtek on the results for a left join.

-LB