Data Comparison between two tables

[#1]

Hi All,

I have two tables A and B and I have to comapre A.Column1 with B.column1 and have to find out what are the nonmatching data in table A.Column1.Below is an example and expected result is 400 and 500. Can you please let me know what would be a best way to do so?

A B
----- --------
100 100
200 200
300 300
400 800
500 900

Thanks,Pooja

[matt_crowther]

There are various methods to achieve this: the simplest may well be to use the exists() function when loading table B. Alternatively you could use the below style:

Compare:
LOAD * INLINE [
F1, TableA
100, A
200, A
400, A
500, A
800, A
900, A
];

Outer Join (Compare)
LOAD * INLINE [
F1, TableB
100, B
200, B
400, B
500, B
600, B
700, B
];

This will give you the following table allowing you to compare to your hearts content:

Hope that helps - if not I'm sure there are many more methods to try.

Matt - Visual Analytics Ltd

[fosuzuki]

Hi Pooja,

I think this can be solved using the 'NOT EXISTS' clause.

Example:

data:
load *
resident A
where not exists (A.Column1, B.Column1);

Hope this helps you.

Regards from Brazil.

Fernando

[#4]

Hi,

Thanks for your response.I tried this but got the error msg(field not found).It couldn't recognise B.column1 .

[#5]

change position of the column name in where clause