Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
guys.. what's the better way to resolve this problem:
In this model, the table TabA join TabB only using the field tabBID. And each table be join with TabC using tabCID each one.
To solve this, I'm thinking to create other tabC, with
other key (tabCID2). And use this new key to join with one of each other tables. Am I on right way?
tks
Hello Rodrigo,
If you want to avoid the synthetic key, you can create composite keys, for example
TabA:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabAID, Afield1FROM tableA; TabB:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabBID, Bfield1FROM tableB; TabC:LOAD tabCID, Cfield1, Cfield2, Cfield3FROM tableC;FROM table;
Anyway, a synthetic key is not an error by itself, it only means that you have more than one table with more than one field named alike.
Hope that helps.
Hello Rodrigo,
If you want to avoid the synthetic key, you can create composite keys, for example
TabA:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabAID, Afield1FROM tableA; TabB:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabBID, Bfield1FROM tableB; TabC:LOAD tabCID, Cfield1, Cfield2, Cfield3FROM tableC;FROM table;
Anyway, a synthetic key is not an error by itself, it only means that you have more than one table with more than one field named alike.
Hope that helps.
This structure needs to be simplified. The specific way would depend a lot on your business needs and on the relations between those tables.
As an example, if Table A and Table B can be combined into a single table (don't worry about de-normalization, we do it successfully every day), then you are only left with two tables joined by a single key, which is fine.
In a different situation, the right solution would be concatenating all three tables into a single fact. Depending on your analysis needs, this may or may not be the right solution...
Hello, Miguel!
I understood your diea, but the problem is that tables TabB and TabC are linked only using tabBID, and not a composite key like tabBID+tabCID.
Oleg,
If I use "join" and "concatenate" commands, would I lost data? Or can I solve this using LEFT, RIGHT and FULL joins?
tks
Rodrigo;
I came across your thread looking for composite key help, but I can answer your question to Oleg -
You won't lose data. Without knowledge of the contents of your tables I can't gauruntee it but we used to have a similar problem to what you are having and solved it by concatenating two tables and left joining another. Works a treat and having all the data in one table improved performance.
You will never lose data using COncatenate (it's appending one table to another, similar to SQL term "UNION ALL", only allows different table structure).
With LEFT, RIGHT or INNER join, you may lose some data that doesn't exist in the the "primary" table (the type of the join determines what table is "Primary"). By default, JOIN is outer, so you won't lose any data if you use the default.