Getting Unique Records after joining 2 or more tables

[Anonymous]

Hi All,

I have 3 tables as drawn below:

Table 1:

Student	Subjects	Books
A	2	2
B		2
C	3	2
D		2

Table 2:

Student	Subjects	Books
A		2
B	3	2
C	3	2
D		2

Table 3:

Student	Subjects	Books
A	2	2
B	3	2
C		2
D		2

Now my requirement is to get a 4th Table as drawn below after merging the above 3 tables.

Table 4:

Student	Subjects	Books
A	2	2
B	3	2
C	3	2
D		2

TIA

Regards,

Rishikesh

[kaushiknsolanki]

Try this.

Data:

Load * from Table1;

Concatenate

Load * from Table2;

Concatenate

Load * from Table3;

Final:

Noconcatenate

Load Distinct * resident Data;

Drop table Data;

Regards,

Kaushik Solanki

[petter]

This should produce the desired result:

T0: LOAD * INLINE [

Student Subjects Books

A 2 2

B 2

C 3 2

D 2

] (delimiter is spaces);

CONCATENATE LOAD * INLINE [

Student Subjects Books

A 2

B 3 2

C 3 2

D 2

] (delimiter is spaces);

CONCATENATE LOAD * INLINE [

Student Subjects Books

A 2 2

B 3 2

C 2

D 2

] (delimiter is spaces);

T:

NOCONCATENATE

LOAD

  Student,

  Max(Subjects) AS Subjects,

  Only(Books) AS Books

RESIDENT

  T0

GROUP BY

  Student;

DROP TABLE T1;

The CONCATENATE prefix is really not necessary as all the fields in the first three loads are identical.

[antoniotiman]

Hi Petter,

may be

...

LastValue(Subjects) as Subjects

...

instead of Max()

Regards,

Antonio

[Anonymous]

Hi Peter,

In my tables there are around 76 fields but the values differ in only one field.

So should I modify the code provided by you?

Regards,

Rishi