More Example on Self Join
Please can anyone share an example for self join. As the data retrieved from the given example is somewhat confusing.
linking the example:
So I answered a question related to this here:
But in general join a table to itself is that same as join two different tables together, you have access to both sides of the table for the rows that you join (based on your ON clause)..
As to why you want todo this, normally you have many rows related to some events. And you want to count how many other related event happens.
So in the example on the tutorial, finding all companies that where funded in GBR, but also funded in JPN first. The first part is done with a WHERE clause on first table, and the second two clause (JPN and BEFORE) are done on the ON clause of the join. As an inner join is used only rows that match all restrictions are returned. But given a company may also have been invested in multiple times in JPN prior to a GBR investment (or many GBR investments) a DISTINCT is used.
The tutorial could also be done with two CTE's (running the distinct on those) and join those results or with sub-selects.Reply