Self-join sql equivalence
Hi,
I was wondering the sql equivalence of a self-join. As far as I know, a table is joined with itself and its code is something like below;
SELECT A.id, B.id, A.value
FROM test_table A, test_table B
WHERE A.id = B.id
AND ...
It would be great to learn the missing part in the condition, thanks.
Tuğrul
-
Hi Tugrul,
I'd suggest a resource such as w3schools to research SQL topics. Here's their documentation on SQL Self JOIN. See the "SQL Self JOIN Example" about half way down the page.
You can also very easily create a self join in a workbook using our join tool with the drag and drop UI once a Data Link has been created or data has been imported with an Import Job.
Alan
-
Hi Alan,
Thanks for the answer. When I applied self join on data by datameer and sql query, the results were different, therefor I thought self-join implementation might be different on datameer. I used the sample data provided by w3schools;
You can see the self-join results of the data below, by sql query and datameer respectively;
As far as I understand from these results, after self_join, datameer eliminates rows when the different rows have the same column values even if they are on different columns, and leaves one row from each group. -
Tugrul,
I spoke to engineering and it turns out at this time we do not support multi-column self joins with custom SQL. We do have an improvement request known as DAP-7226 that will add this functionality, but it is currently not scheduled for a sprint so an ETA cannot be provided.
At this point in time I would suggest importing the full data and performing your joins with the join tool within a workbook. This should allow you to get the join you're looking for.
Alan
Please sign in to leave a comment.
Comments
3 comments