Match records from 2 tables and exclude records that dosen't match between

[saminouari]

Hi,

I want to match records from the two tables, and exlude records from Table 2 that dosen't match records from Table 1.

What it looks like :

Table 1:	Table 2:
Load*inline	Load*inline
[Shop name,Qty	[Shop name,Qty
Store A,2	Store A,2
Store A,1	Store A,1
Store A,1	Store A,3
Store C,2	Store D,2
Store C,1	Store D,1
Store B,1	Store B,1
Store B,2];	Store B,2
	Store F,3
	Store F,2
	Store F,1
	Store F,3];

What I want it look like :

Table 1:		Table 2:
Load*inline		Load*inline
[Shop name,Qty		[Shop name,Qty
Store A,2		Store A,2
Store A,1		Store A,1
Store A,1		Store A,3
Store C,2		Store D,2
Store C,1		Store D,1
Store B,1		Store B,1
Store B,2];		Store B,2];

[YoussefBelloum]

Hi,

you will need to make a left join on Table1

example:
Table1:
LOAD TABLE 1

FROM TABLE1...

LEFT JOIN (TABLE1)
LOAD...

FROM TABLE2...

[rogerpegler · Accepted solution]

@YoussefBelloum  solution is correct if you want a merged table.

If you want to keep them separate user innr join eg

Inner Join (Table2)

Load Distinct Shop name,Qty Resident Table1

The 'Distinct' clause ensures you don't accidentally create duplicate records in Table 2.

[saminouari]

It's exactly what I wanted. Thank you @YoussefBelloum it worked like a charm 

[YoussefBelloum]

Hi @rogerpegler 

Changing the join type from LEFT to INNER don't change nothing on merging the tables or keeping them separate, it only change the way you join, and with INNER, LEFT or RIGHT JOIN, tables will be merged.

To keep the tables separate, you should use the KEEP statement preceded with the join type.. eg: Left Keep or Inner Keep