Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

Can't have more than 1 column in subquery (but he told us we could?!) sales by gender question.

https://teamtreehouse.com/library/querying-relational-databases/subqueries/subqueries Challenge 4.

My code:

SELECT s.SaleID, s.CarID, s.CustomerID, s.LocationID, s.SalesRepID, s.SaleAmount, s.SaleDate FROM Sale AS s
INNER JOIN (SELECT CustomerID, Gender FROM Customer) AS c
ON c.CustomerID = s.CustomerID
WHERE c.Gender = 'F';

Error: Can't have more than 1 column in the subquery

Andrew Chalkley said we can't use multiple columns when using IN, but we can if we're joining an Aliased table.

I've tested this by adding the Gender column and removing the WHERE clause. My code produces the correct output.

Does anyone know why I get this error?

Steven Parker
Steven Parker
221,291 Points

You didn't provide a link to the course page. Is this error an SQL issue, or an error from a challenge validation?

Edited question

Error from challenge validation.

I've tried putting the WHERE clause inside the subquery, SELECTing * FROM Customer and several other variations that all produce the correct set of results.

2 Answers

Steven Parker
Steven Parker
221,291 Points

The challenges often have specific validation criteria, sometimes more implicit than explicit in the instructions. For example, when it asks for "all rows" from a table, it's generally expecting you to use the "*" shorthand instead of listing individual columns.

And while your multi-column solution does technically get the job done, the challenge is looking for a solution that uses a single-column derived table. You can easily accommodate this by moving your WHERE clause into the subquery.

That said, a query in the format:

SELECT * FROM table
JOIN (SELECT id FROM other_table WHERE criteria)
ON table.other_table_id = other_table.id

will return all columns, including that from the subquery on other_table, which the question explicitly tells you not to return (which is why I brought back the columns in the way I did).

Thanks for your input, I'll see if any other ways to bring back only the columns from the first table work.

Steven Parker
Steven Parker
221,291 Points

You can limit the result set by doing this:

SELECT table.* FROM table JOIN ...

But despite the instructions, I'm pretty sure this challenge is expecting just "SELECT * ...". Try it and see if you pass the challenge.

You might consider this a course bug, and if so you may want to report it to Support.

Yeah, turns out it just wants you to ignore the question and return the extra column.

Thanks for your help

Steven Parker
Steven Parker
221,291 Points

Getting it corrected could help future students. And if you're the first to report a legitimate bug, you'll get the "Exterminator" badge. :beetle: