Many to one table join

[#1]

Hi,

I’m looking to combine 2 tables  together Consumption and Site.

The id that I want to combine them with is MPRN however Site will only contain one instance of each MPRN value whereas Consumption will have many.

Example:

Consumption

MPRN	Consumption	Date
123	80	01/01/2015
123	90	01/12/2014
356	100	01/12/2014

Site

MPRN	Name	Active
123	ABC	Y

To be merged into:

MPRN	Name	Active	Consumption	Date
123	ABC	Y	80	01/01/2015
123	ABC	Y	90	01/12/2014

Does anyone know how to achieve this?

Any help is much appreciated.

[PrashantSangle]

Hi,

Simple Left join or join will work for you.

Or

Simply load both table due to association relation is created between Consumption and Site

Then use Stright table and those field.

Regards

[vikasmahajan · Accepted solution]

PFA uses Right join

Vikas

[Anonymous]

Hi Ashley,

Try below code:

Consumption:
Load * inline
[
MPRN,Consumption,Date
123,80,01/01/2015
123,90,01/12/2014
356,100,01/12/2014
];

Site:
Load * inline
[
MPRN,Name,Active
123,ABC,Y
];

NoConcatenate

SiteConsumption:
LOAD *
Resident Site;

left Join

LOAD *
Resident Consumption;

drop table Consumption;
drop table Site;

Regards

Neetha

[its_anandrjs]

Hi,

Do simple Right join and why not do only right join rather than load resident tables because it increase the load time as well.

Ex:-

Consumption:
Load * inline
[
MPRN,Consumption,Date
123,80,01/01/2015
123,90,01/12/2014
356,100,01/12/2014
];

Site:
Right Join

Load * inline
[
MPRN,Name,Active
123,ABC,Y
];

RENAME Table Consumption to FinalTable;

Regards

Anand

[#6]

Thanks for the help everyone - right join worked.  The reason it wasn't working correcting was because the MPRN field on Site had whitespace before the value!