Session Ready
Exercise

Coalesce with a self-join

You previously joined the company and fortune500 tables to find out which companies are in both tables. Now, also include companies from company that are subsidiaries of Fortune 500 companies as well.

To include subsidiaries, you will need to join company to itself to associate a subsidiary with its parent company's information. To do this self-join, use two different aliases for company.

coalesce will help you combine the two ticker columns in the result of the self-join to join to fortune500.

Instructions
100 XP
  • Join company to itself to add information about a company's parent to the original company's information.
  • Use coalesce to get the parent company ticker if available and the original company ticker otherwise.
  • INNER JOIN to fortune500 using the ticker.
  • Select original company name, fortune500 title and rank.