Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Querying Relational Databases Subqueries Subqueries

Taylor Han
Taylor Han
2,406 Points
Posted by Taylor Han
Taylor Han
2,406 Points

Subquery Task 4 (with alias?)

In a car database there is a Sale table with columns, SaleID, CarID, CustomerID, LocationID, SalesRepID, SaleAmount and SaleDate and a Customer table with columns, CustomerID, FirstName, LastName, Gender and SSN.

Use a subquery as a derived table to show all sales to female ('F') customers. Select all columns from the Sale table only.

What I tried:

SELECT * FROM Sale AS derived_table INNER JOIN (SELECT CustomerID FROM Customer WHERE Gender = "F") ON Sale.CustomerID = Customer.CustomerID;

It says I am missing a table alias, did I alias it in the wrong place? Thanks!

2 Answers

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

This code is applying the alias to the named table (Sale).
The requested alias would be placed immediately after the expression that defines the other table.

Taylor Han
Taylor Han
2,406 Points
Taylor Han
Taylor Han
2,406 Points

SELECT * FROM Sale INNER JOIN (SELECT* FROM Customer AS derived_table WHERE Gender = "F" AS derived_table) ON Sale.CustomerID = Customer.CustomerID;

second attempt, but still missing a table alias. I guess I'm just confused as to what the other table is. Is it not Customer?

Thank you!

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

Now there's a different column alias, plus an alais after that one in the wrong place.

That 2nd one would become a table alias if you move it to the other side of the closing parenthesis.

Then you'd need to use that alias instead fo "Customer" in the ON expression (there's no "Customer" table anyway).

Taylor Han
Taylor Han
2,406 Points
Taylor Han
Taylor Han
2,406 Points

SELECT * FROM Sale INNER JOIN (SELECT CustomerID FROM Customer WHERE Gender = "F") AS derived_table ON Sale.CustomerID =derived_table.CustomerID;ā€‹

THANK YOU!

ā€‹