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

Posted by Luke Hargraves
Luke Hargraves
4,968 Points

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
230,274 Points
Steven Parker
Steven Parker
230,274 Points

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

Luke Hargraves
Luke Hargraves
4,968 Points

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
230,274 Points
Steven Parker
Steven Parker
230,274 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.

Luke Hargraves
Luke Hargraves
4,968 Points

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
230,274 Points
Steven Parker
Steven Parker
230,274 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.

Luke Hargraves
Luke Hargraves
4,968 Points
Luke Hargraves
Luke Hargraves
4,968 Points

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

Thanks for your help

Steven Parker
Steven Parker
230,274 Points
Steven Parker
Steven Parker
230,274 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: