Help in doing a Join on multiple fields. Join with * if it has no values.

[suraj_spins]

Hi All,

I am trying to join two tables with multiple columns. A regular join but if values are missing in one of the columns it should join on all possible field attributes. If not null then consider the value in the field. Below is the example. Any help is appreciated, Thank you.

A:

A	B	C	D	E
a	IL	a44	30	blue
b	CA	s66	31	yellow
c	NY	n28	32	Green
d	FL	b36	33	Red
e	CO	l50	34	Red
a	AZ	a44	35	blue
b	MN	s66	36	yellow
c	CA	n28	37	Green
d	MA	b36	38	Red
e	NV	l50	39	Red

B:

A	B	C	D	E	F
a		a44		blue	Nike
b	CA	s66	31	yellow	Puma
c	CA	n28		Green	Reebok
d		b36		Red	Nike
e	NV	l50		Red	Adidas

Result:

A	B	C	D	E	F
a	IL	a44	30	blue	Nike
b	CA	s66	31	yellow	Puma
c	NY	n28	32	Green
d	FL	b36	33	Red	Nike
e	CO	l50	34	Red
a	AZ	a44	35	blue	Nike
b	MN	s66	36	yellow
c	CA	n28	37	Green	Reebok
d	MA	b36	38	Red	Nike
e	NV	l50	39	Red	Adidas

[Anonymous]

if you do it like that you have 5 Key Fields,

the matching will only work if all of the 5 fields match.

So what is/are your key field(s)?