Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm just having a navigation problem in my own thoughts 😉
I have two tables, say A and B.
=> In table A, the key is unique
=> In table B, the key is not unique
=> I want to make them into one table using a LEFT JOIN
==>> D I have to JOIN table B to table A or the other way round now?
(I guess I'd have to join the table with the unique key to the one with the non_unique key, no?)
Thanks a lot!
Best regards,
DataNibbler
I don't think it matters, except that the left join will use the first table for the filtering. Are all the keys present in both files? If yes, then it does not matter. If no, then the first table would generally be the one with the most complete set of the join key(s).
Either way, for each key included in the final file, you will land up with the same number rows as there are in table B with that key value.
Hi,
I think you can create your join without problems, independence of the way, because you'll create a new table.
Regards
I don't think it matters, except that the left join will use the first table for the filtering. Are all the keys present in both files? If yes, then it does not matter. If no, then the first table would generally be the one with the most complete set of the join key(s).
Either way, for each key included in the final file, you will land up with the same number rows as there are in table B with that key value.
I'd say table A into table B. That way you add through a unique key into a table with more possibilities.
The other way around would make you lose data you may not want.
Hi jonathan,
okay. Every key in table B can be joined to a key in table A, I'm sure of that - it should in fact be so, so it was safe to use a LEFT KEEP before to mirror the time-wise limitation I have in table A because table B does not have a date_field.
If I understand you correctly, the general rule is that the primary table (the one that I put in brackets, that I join TO) should be the onewhere the key is non_unique, just as I have done it, is that right?
I will do it that way then, even if it doesn't really matter in this specific instance.
Best regards,
DataNibbler
I don't think that 'uniquess' matters in the order and direction of the join. There may be performance differences between the two alternatives, but the final result will be the same.
Hi jonathan,
now I have something rather extraordinary which puzzles me a bit because I'm not so knowledgeable in the processes and stuff ...
Remaining with the example of table A and table B, I now have a situation where
- table A has approx. 275.000 records, but still the key is unique.
- table B has approx. 269.000 records, but the key is non_unique
=> I LEFT JOIN table A to table B now, so I go
>> LEFT JOIN (table_B) ... <<
<=> The resulting table has the same nr. of records as table B, approx. 269.000. I wonder if that can be correct?
That is a question I cannot answer myself because I don't know much about the relation between the data in those tables. I'll ask someone from the Engineering_team who should know.
Hi,
You join A into B with a left join, so you take all lines from table B (and only table B), and you add the variables from table A with the same unique key. It is then normal that you get the same number of lines as a result.
Here's (part of) the reference document I always use in these instances.
Kind regards,
Johan
Assuming that table A has a full set of the possible keys (in both A and B):
If you LOAD B and Left Join LOAD A, you will get the number of rows in B.
If you LOAD A and Left Join B, you will get something more than the number of rows in the original Table A.