Display common records from different tables

[pala_jagadeesh]

Hi,

There are three tables Teams,Team1,Team2

I want to compare records between these tables

Teams

A;B;C;D

AB;BC;CD;DE

CF;DF;GF;AS

AF;AH;AG;AJ

Team1

A

AB

__

__

Team2

A

AB

__

AJ

Output

	Teams	Team1	Team2
A	yes	yes	yes
AB	Yes	yes	yes
CF;DF;GF;AS	yes	No	No
AF;AH;AG;AJ	Yes		yes

[justISO]

Hi, in load script you can try something like this:

temp1:
load * inline [
Teams
A;B;C;D
AB;BC;CD;DE
CF;DF;GF;AS
AF;AH;AG;AJ  ];

temp2:
load * inline [
Team1
A
AB
__
__  ];

temp3:
load * inline [
Team2
A
AB
__
AJ  ];

//---SOLUTION--->
Main:
NoConcatenate
LOAD Distinct
Teams as origTeams,
subfield(Teams,';') as subTeam,
'Yes' as Teams
RESIDENT temp1;

JOIN
LOAD Distinct
Team1 as subTeam,
'Yes' as Team1
RESIDENT temp2;

JOIN
LOAD Distinct
Team2 as subTeam,
'Yes' as Team2
RESIDENT temp3;

DROP TABLES temp1, temp2, temp3;