Unravelling a simple table

[EnergyTed]

Hi

I have a simple table with two fields.

Field 1 - Meter ID, Field 2 - Sub Meter ID.

The table shows all the meter/sub meter links in an unknown amount of networks.

Some meter ids are repeated in field 1 to show all the sub meters linked to it. Some records are Subs of a sub meter and not a main meter.

Can anyone help to explain this out to show all linked meters in any/all individual networks.

Kind Regards

 

[fneily]

Assuming a meter can only be a submeter to one meter, change your table to:
MeterID, MotherMeterID

MeterID would be a primary key, so listed only once.
MotherMeterID would be the MeterID of its immediate parent.

Then using a self-join, you can find a meter and all its submeters.