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

SQL Error: no such column: first_name [RESOLVED]

Why isn't this a valid column name?

Link to challenge: https://teamtreehouse.com/library/reporting-with-sql/working-with-text/concatenating-text

Challenge Task 1 of 2

In the library database there's a patrons table listing all the users of the library.

The columns are id, first_name, last_name, address, email, library_id and zip_code.

Generate a list of strings that are in the following format: Andrew Chalkley andrew@teamtreehouse.com. Concatenate the first name, last name and email address for all users.

Alias it to to_field. This will be used in the "To" field in email marketing


Figured it out.

It WAS a valid column name as long as you use 'FROM patrons'

so it knows which table the SELECT statement is referring to:

SELECT first_name || ' '  || last_name || ' <' || email || '>' AS to_field FROM patrons;

Now I'm having trouble with the second part of the challenge:

Challenge Task 2 of 2

In an eCommerce database there's a customers table.

There is an id, nickname, street, city, state, zip, country and user_id columns.

Concatenate the street, city, state, zip and country in the following format. Street, City, State Zip. Country e.g. 34 NE 12 st, Portland, OR 97129. USA. Alias the concatenated string as address

The SQL I tried was:

SELECT street || ', ' || city || ', ' || state || ' ' || zip || '. ' || country AS address FROM customers;

It gives me an error:

SQL Error: no such column: street 

So this seems to be a different issue than the first part of the challenge

because I am using 'FROM customers' at the end of the query

but maybe that FROM part is getting cutoff somehow?

Because of customers. Instead of customers; you should typed in addresses;

7 Answers

Andrew Winkler
Andrew Winkler
37,737 Points

I FIGURED BOTH SOLUTIONS OUT!!!

Challenge One:

In the library database there's a patrons table listing all the users of the library. The columns are id, first_name, last_name, address, email, library_id and zip_code. Generate a list of strings that are in the following format: Andrew Chalkley andrew@teamtreehouse.com. Concatenate the first name, last name and email address for all users. Alias it to to_field. This will be used in the "To" field in email marketing.

SELECT first_name || ' '  || last_name || ' <' || email || '>' 
AS to_field FROM patrons;

Challenge Two:

In an ecommerce database there's a addresses table. There is an id, nickname, street, city, state, zip, country and user_id columns. Concatenate the street, city, state, zip and country in the following format. Street, City, State Zip. Country e.g. 34 NE 12 st, Portland, OR 97129. USA. Alias the concatenated string as address

SELECT street || ", " || city || ", " || state || " " || zip || ". " || country 
AS address FROM addresses;

The secound solution requires very strange formatting. Read the error messages if you're stuggling. Then tinker.

Zulma Feliciano
Zulma Feliciano
3,223 Points

I got in touch with the instructor, and he said the wrong table name was given; should have been addresses table. Issue has been corrected.

Giulio Vannini
Giulio Vannini
21,922 Points

Use double quotes:

SELECT street || ", " || city || ", " || state || " " || zip || ". " || country AS address FROM customers;

However it returns a long list of errors and a NOT FOUND on the bottom!

I guess that needs just to be fixed :)

Zulma Feliciano
Zulma Feliciano
3,223 Points

Having the same exact issue.

I'm also getting error messages for what I believe is the correct query

Thanks Zulma for contacting the instructor.

Thanks Andrew..yes I agree.

The answer to the second part of the challenge does require some 'requires very strange formatting' indeed!

I have marked Andrew's answer as Best Answer.

Andrew Winkler
Andrew Winkler
37,737 Points

Thank you. It's very much appreciated. I hope to be a forum moderator someday... cough shameless plug Andrew Chalkley cough, cough

Where did you get those "<" & ">" signs from?

It was part of the email statement e.g first_name last_name < andrew@teamtreehouse.com > the '<' and '>' are for the start and end of the email address.

So that's why in the code we write || '<' || email || '>'
This is to display the '<' and the email address with a closing '>' .

:)