Find a Column Value in another table

[risabhroy_03]

Hey,

I have a straight table which looks like below and I want to search for my Customer Code in another table(Column Name in that table - "T$ITBP".

If My Customer Code is present in that table(T$ITBP), then I have to keep that row in my table else I have to remove that row.

I tried using Left Join based on Company Code and then used if exists , but I am getting lots of duplicate records.

[sidhiq91]

@risabhroy_03  Did you try with Inner Join, I guess that should sort out the issue.

Else Please provide data for us to work out.

[risabhroy_03]

Can you please help  me in writing the script

[sidhiq91]

@risabhroy_03  Yes I can. Please provide some sample data from both these tables.

[risabhroy_03]

My original Table - 

Company_Code, Customer Code

215, ICB001

215, ICB002

215,ICB003

225,ICB001

225,ICB009

240,ICB001

240,ICB007

Another Table

Company_Code, T$ITBP

215, ICB001

215, ICB002

215,ICB003

225,ICB001

225,ICB064

240,ICB001

240,ICB016

So my Customer Code should check in T$ITBP. If it exists then I have to take that row else I don't have to take that record in my original table.

Resultant Original Table

Company_Code, Customer Code

215, ICB001

215, ICB002

215,ICB003

225,ICB001

240,ICB001

[Mark_Little]

Hi, Try the below

Table1:

Load

Company_Code&Customer Code as Key

Company_Code,

Customer Code

From Table 1;

Left Join

LOAD

Company_Code&T$ITBP as KEY

1 as Flag

From Table 2;

You can you use the flag to find the one exist in both tables

[risabhroy_03]

Ok agreed.

But now I want that these should not be applicable for Company_Code=320.

I mean it should check for all Company_Code except 320, I want my 320 data to come as it is.

Thenn what should we do?

[sidhiq91]

@risabhroy_03  Please try the below code.

NoConcatenate
Main:
Load *,
AutoNumber(Company_Code&[Customer Code]) as Key
Inline [
Company_Code, Customer Code

215, ICB001

215, ICB002

215,ICB003

225,ICB001

225,ICB009

240,ICB001

240,ICB007
];

Inner join(Main)
Load Company_Code as Ccode,
T$ITBP,
AutoNumber(Company_Code&[T$ITBP]) as Key

inline [
Company_Code, T$ITBP
215, ICB001

215, ICB002

215,ICB003

225,ICB001

225,ICB064

240,ICB001

240,ICB016

];

exit script;

If this resolves the issue please like and accept it as a solution