How to compare rows between two tables

Four Stars

How to compare rows between two tables

Hello, so I have two table with the same columns, but no primary key.

Table 1

 Name |

Tom |

Albert |

Tom |

Hawk | 

Albert | 

Harry | 

 

Table 2

Name | 

Tania |

Albert | 

Tom |

Peter |

 

Essentially, I want to have 2 different outputs.
First output will be a table with names in Table 1 that can be found in Table 2

Output 1

Name |

Tom |

Albert |

Tom |

Albert | 

 

 

Second output will be a table with names in Table 1 that cannot be found in Table 2

Output 2

 Name |

 

Hawk | 

Harry | 

 

I have tried using tMap but I have no idea how I could go around doing it.

Forteen Stars TRF
Forteen Stars

Re: How to compare rows between two tables

You just need 2 subjobs.

1rst one use Table1 as main row and Table2 as lookup with a tMap and inner join - rejeected rows will go to outpu1.

2nd one is just the reverse to the previous one.

 

The general design should be like this:

Capture.PNG

Here is what the tMap should look like (be carefull of red circled options):

Capture.PNG

The other tMap must be the same as this one. 

 


TRF