Get Started

Using alias to handle same table joined queries

Often, you'll have tables that contain hierarchical data, such as employees and managers who are also employees. For this reason, you may wish to join a table to itself on different columns. The .alias() method, which creates a copy of a table, helps accomplish this task. Because it's the same table, you only need a where clause to specify the join condition.

Here, you'll use the .alias() method to build a query to join the employees table against itself to determine to whom everyone reports.

This is a part of the course

“Introduction to Databases in Python”

View Course

Exercise instructions

  • Save an alias of the employees table as managers. To do so, apply the method .alias() to employees.
  • Build a query to select the employee's name and their manager's name. The manager's name has already been selected for you. Use label to label the name column of employees as 'employee'.
  • Append a where clause to stmt to match where the id column of the managers table corresponds to the mgr column of the employees table.
  • Order the statement by the name column of the managers table.
  • Execute the statement and store all the results. This code is already written. Submit the answer to print the names of the managers and all their employees.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Make an alias of the employees table: managers
managers = ____

# Build a query to select names of managers and their employees: stmt
stmt = select(
    [managers.columns.name.label('manager'),
     ____]
)

# Match managers id with employees mgr: stmt_matched
stmt_matched = stmt.where(managers.columns.id == ____)

# Order the statement by the managers name: stmt_ordered
stmt_ordered = stmt_matched.order_by(____)

# Execute statement: results
results = connection.execute(stmt_ordered).fetchall()

# Print records
for record in results:
    print(record)
Edit and Run Code

This exercise is part of the course

Introduction to Databases in Python

IntermediateSkill Level
4.4+
14 reviews

In this course, you'll learn the basics of relational databases and how to interact with them.

Start Course for Free

In this chapter, you will learn to perform advanced—and incredibly useful—queries that enable you to interact with your data in powerful ways.

Exercise 1: Calculating values in a queryExercise 2: Connecting to a MySQL databaseExercise 3: Calculating a difference between two columnsExercise 4: Determining the overall percentage of womenExercise 5: SQL relationshipsExercise 6: Automatic joins with an established relationshipExercise 7: JoinsExercise 8: More practice with joinsExercise 9: Working with hierarchical tablesExercise 10: Using alias to handle same table joined queries
Exercise 11: Leveraging functions and group_bys with hierarchical dataExercise 12: Handling large ResultSetsExercise 13: Working on blocks of records

What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.

Start Learning for Free