subtraction of two tables

[marcel_olmo]

I think there is an easy way to solve this, but by the moment I couldn't find it, I tried with combinations of JOIN, but I think this is not the correct way to solve it.

Here is my challenge :

I have two tables : Table1 and Table2.

Table1:

Table2:

I can't use the JOINS function (left, right, inner, outer), because they put in the result table all the possible combinations because of 'A' has the same value in all the rows.

My wrong result and I don't know how to handle is this :

And my desired result is to get all the records on Table1 that they are not in Table2. It would be :

A , B

10 , 300

10, 400

10, 500

10, 600

10, 700

10, 800

10, 900

10, 1000

I've attached a file to clarify what I'm trying to say.

Thank you very much in advance.

Regards

[syed_muzammil · Accepted solution]

Hi,

You can load the Table2 frist and then load Table1 with the Where Not Exists function. This will load only those records not present in Table2.

Regards,

Syed.

[marcel_olmo]

Thanks for your quick reply Syed.

I'm sorry because I don't understand you very well.

How can I do in this case the "not exists function?", because the values of A are always the same, and B and C are diferent columns. So I don't know how can I put a NOT EXISTS here.

Could you be more specific?

Many thanks anyway.

[marcel_olmo]

Ok, finally I found the solution thanks to Syed.

First I have to Load the subtraction Table (the table I want to subtract from a Base table) :

SubtractionTable:

load * inline [

A, B

10, 100

10 , 500

];

Then, I load the Base Table :

BaseTable:

load * inline [

A , C

10 , 100

10 , 200

10 , 300

10 , 400

10 , 500

10 , 600

10 , 700

10 , 800

10 , 900

10 , 1000


] where not exists(B,C);

And you're gonna have the desired result : all the records of the Base Table which they aren't in the Subtraction Table.

I've attached the file with the desired result. It could be useful for others.

[nathanfurby]

If you want to exclude all similar values in columns B and C without taking into account column A then you can do it in the way suggested.

If you only want to exclude values where both columns A and B match columns A and C then I think you would need to include some kind of 'key' check. I have attached an example if you are interested.

[marcel_olmo]

Many thanks Nathan for your contribution, you make more complete the previous solution.

In my case, the previous solution was fine, but you're right, if you want to check it more clearly, making a unique key with both values, I'm pretty sure you can avoid future problems doing that.

See you around.

Thanks.