Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a question to the topic: "Join".
I have two tables which look like that:
table1
ID | Price |
---|---|
1212 | 17,40 |
1213 | 15,30 |
1214 | 28,30 |
table2
ID | Group |
---|---|
1212 | Banana |
1212 | Apple |
1213 | Lemon |
Now I joined table2 with "left join" into table1.
My problem is, that after the joining the ID 1212 will be doubled as there are two groups (Banana and Apple) possible. Now if I sum the prices of the different IDs, the result of ID 1212 is wrong cause of the problem.(17,40 +17,40)
Do you have an idea how to solve this problem? Can I use another join, so that e.g. Apple is not joined and only banana 🙂 ??
If you need further information, just let me know!
Thank you for helping!
Hello,
You should create a unique key in table 2 and use that in table 1. Another idea is to create a super group and add the column "group" as a description:
ID | super group | description |
---|---|---|
1212 | Banana-Apple | Bananas and apples |
1213 | Lemon-Oranges | Lemon |
I hope this helps.
J
Do you have an idea how to solve this problem? Can I use another join, so that e.g. Apple is not joined and only banana 🙂 ??
Sure, you can probably create a modified table2 with a distinct ID, where you have to decide which Group value to use.
But have you tried, not joining the tables, instead keep them just linked / associated?
You can use left keep instead of left join
You can try using a different aggregation function. For example avg(Price) instead of sum(Price)
Or you can try something like this:
Table:
Load ID, Price
from source1;
left join
Load ID, firstvalue(Group) as Group
from source2
group by ID;
Hello,
You should create a unique key in table 2 and use that in table 1. Another idea is to create a super group and add the column "group" as a description:
ID | super group | description |
---|---|---|
1212 | Banana-Apple | Bananas and apples |
1213 | Lemon-Oranges | Lemon |
I hope this helps.
J
Thank you very much for your answers.
The "left keep" didn't work!