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

PHP

Posted by Jeff Busch
Jeff Busch
19,287 Points

SQL Why won't this work?

Hi Treehouse,

I was playing with some SQL from the Database Foundations track and got a little confused.

Why won't this work?

SELECT first_name,
UPPER(last_name), LOWER(email),
LENGTH(username) FROM users
HAVING username < 19;

But this will.

SELECT first_name, UPPER(last_name),
LOWER(email), LENGTH(username)
AS username_length FROM users
HAVING username_length < 19;

MySQL Workbench says: Error Code 1054 Unknown column 'username' in 'having clause'.

If the second example works then obviously there is a column username.

Thanks, Jeff

3 Answers

Nathan Newell
Nathan Newell
7,634 Points
Nathan Newell
Nathan Newell
7,634 Points

Try 

HAVING LENGTH(username) < 19;
Matthew Bretag
Matthew Bretag
4,978 Points
Matthew Bretag
Matthew Bretag
4,978 Points

Hi Jeff,

Because you're not selecting 'username' but LENGTH(username) then the HAVING part of the query has no reference for username. If you wanted you could use Nathan's response above or alternatively the following would also work:

SELECT first_name,
UPPER(last_name), LOWER(email),
LENGTH(username) as username FROM users
HAVING username < 19;
Jeff Busch
Jeff Busch
19,287 Points
Jeff Busch
Jeff Busch
19,287 Points

Hi Guys,

Thank you for replying. Nathan, your suggestion produced the same error message: Unknown column 'username' in 'having clause'. Mathew, your suggestion worked but it's six of one and half a dozen of the other. I guess if I start referring to a column as LENGTH(something) I have to keep referring to it the same throughout the statement unless I change the name with an AS keyword. Then I have to continue with that name throughout the statement. Actually, it appears that LENGTH() can't be used following HAVING, try it.

This isn't really an issue, I was just curious as to why. I guess it is what it is.

Jeff