how to join three tables that create a circular reference

[#1]

I need to join three tables together, but when I do, it creates a circular reference. Everything I have read about circular references and how to avoid them doesn't take into account what happens if you actually want all three tables to be joined. Here is the data that I need joined:

From Tickets Table:

AreaGuid

SeatIdInAreaMap

From Seats Table:

SeatIdInAreaMap

AreaMapGuid

From Area Maps Table:

AreaGuid

AreaMapGuid

I have tried concatenating two tables together and then creating a link table to join them, but I have run into problems creating a key because of the unique identifier data type.

I successfully created a MS Access make table query that puts everything I need into one table, however I need this to go on a server where it will be set to automatically reload every 30 minutes. So alternatively, if anyone has a solution for how to automatically initiate the query every 30 minutes, that would work as well.

[marcus_sommer]

Maybe Mapping as an Alternative to Joining could be the solution. If not please provide a small example with a few inline-data then from your example it's not clear how to join these tables or if it might be better to join two tables at first and the concatenate the third ...

- Marcus

[Anonymous]

Hi Tania,

Can you share your qvw file, so we can see in which order you concatenate/join the tables ?

[Peter_Cammaert]

It makes no sense trying to fix this at the lowest level (LOAD statements) without knowledge of the expected data model.and the nature and relations of those data sets. Can you explain a bit more what these tables represent and how you would like to make use of their content? Thanks.

Best,

Peter

[Anonymous]

Use Link Table

[Anonymous · Accepted solution]

Hi Tania,

With this code you will have only one table with all fields and recs.

AllTable:

load  

    AreaGuid,

    SeatIdInAreaMap

resident TicketsTable;

Left join (AllTable)

LOAD

    SeatIdInAreaMap,

    AreaMapGuid

resident SeatsTable;

join (AllTable)

load

    AreaGuid,

    AreaMapGuid

resident AreaTable;

Regards!

[Anonymous]

Check this link. May be helpful

Circular References

[#8]

This worked. Thank you!