Connecting two table and displaying only matched without joins.

[LP27]

Hi Everyone,

I need guidance for the below scenario -

I have got two tables and there is one common field in both the tables, but I want to display only matching values from common fields.

For Example : 

Table A:

ID	Name	Age
001	ABC	1
002	BCD	2
003	ZXY	2

Table B:

NO	Name	Age	Location
001	ABC	4
002	BCD	5
305	DEF	3
405	GHI	2
003	ZXY	2

As per the above tables- ID and NO are the two matching columns, Now i need to display in Table B only records which are present in Table A.

Output Table B : 

NO	Name	Age	Location
001	ABC	4
002	BCD	5
003	ZXY	2

As per my understanding, we can do it with Exists Condition. I dont want to join tables or Drop Tables.

Please let me know your views.

Thanks in Advance! 

[tresesco]

Did you try with exists()?  Like:

Load * from tableA;

Load * from tableB where Where Exists(ID, NO);

[LP27]

Yes I tried like this,:

Table B:

No as Code,

Name,

Age,

Location

From(path);

Table A:

Load

ID as code,

Name,

Age,

from (Path)

Where exists(Code,ID);

Was not successful with the results! 

[LP27]

Thanks for reply!

If I try the way you say, it generating Zero values from Tables B and Generating all the values from Table B.

[anushree1]

You must use the condition : Where exists(ID,Code);

Please use the script as below and check it must work, also attaching the sample for reference:

A:
LOAD
ID,
Name,
"Age"
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @1);

B:
LOAD
"NO",
Name,
"Age",
Location
FROM [lib://Web_Connection]
(html, utf8, embedded labels, table is @2)
where Exists( ID,NO);

Drop table A;

[LP27]

I tried as you say, Its generating zero values from Table B.

[anushree1]

Did you check the attachment i have shared

[LP27]

Yes I did, But there you have dropped Table A,  but I would needing other columns from Table A so Wont be able to drop.

[anushree1]

if you need Table A you can keep it too, I dropped it for removing Synthetic keys, so if you want Table A jus rename Fields Name and Age in Table A and you must be good to go

[LP27]

So this is my script-

Table A:
LOAD
ID as Code,
Name,
"Type",
"PM Code",
applymap('P_Map', "PM Code",'Other') as "PM",
applymap('BMap', "PM Code",'Other') as "BU",
applymap('SMap', "PM Code",'Other') as "Seg",
FROM [Path];

Table B:
LOAD
No as Code,
"Part or Series #",
"Program 1",
Notes,
"Function Category"
FROM [Path]
where Exists(ID,NO);

This is my original script! not sure how to proceed with this scrip here.