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.

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • linking the example:

    https://community.modeanalytics.com/sql/tutorial/sql-self-joins/

    So I answered a question related to this here:
    https://forum.modeanalytics.com/t/36p96g/sql-self-join-tutorial-example

    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 Like
reply to topic
Like Follow
  • Status Answered
  • 7 days agoLast active
  • 1Replies
  • 44Views
  • 2 Following

Create A New Discussion

Share your thoughts