Join while ignoring nulls in secondary table

[Abhishek_Gupta]

I have a primary table and a secondary table. I need to join the primary table with secondary table to obtain the value of SKU in Primary table. But I want to ignore the join condition for columns for where it  null in secondary table,i.e. if there is null in a certain column there could be any other value in the primary table for that particular column.

I know that this could be achieved using "IIF" in formula tool, but in that case I will have to code each and every row from the secondary table into the formula. And I am having more than 150 rows in the secondary table. Is there any way to achieve the same using a join or something else?

Below are the example for the same:

Primary Table

Brand	Sub Brand	Product	Additional	SKU
b1	sb1	p1	sdf
b2	sb2		sdf
b3	sb3	p2	sdf
b4		p3	sdf
b5			sdf
b6		p4	sdf
		p5	sdf
		p6	sdf
b7		p7	sdf

Secondary Table

Brand	Sub Brand	Prodcuct	SKU
b1	sb1		1
b2	sb2		2
b3	sb3		3
b4			4
b5			5
		p4	6
		p5	7
		p6	8

Join Result

Brand	Sub Brand	Product	Additional	SKU
b1	sb1	p1	sdf	1
b2	sb2		sdf	2
b3	sb3	p2	sdf	3
b4		p3	sdf	4
b5			sdf	5
b6		p4	sdf	6
		p5	sdf	7
		p6	sdf	8
b7		p7	sdf

[patrick_digan]

@Abhishek_Gupta If you have a set number of columns, perhaps the attached could work for you. I just tried joining on brand first, then sub brand, then product. By unioning all the results, I got the desired result. Perhaps your actual situation is more complicated and won't work for this method.

[jrgo · Accepted solution]

@Abhishek_Gupta,

Here's one more option should you still be looking for others...

[Abhishek_Gupta]

@patrick_digan Thanks for the help...But I guess this would become a very complicated solution in my original situation.

[Abhishek_Gupta]

@jrgo This one is a nice solution. Although I achieved the task by using "Conditional statements(IF then)". But I will use this approach in my next task. Thank you