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
rh16
3,781 PointsConcatenation and alias challenge test _reporting with SQL video - concatenation
I cannot get this to work no matter what I try. Here is the question and 1 of my many attempts to get it right.
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.
Type in your command below.
Bummer! There's something wrong with your SQL statement. Please review your code and try again.
I think my problem has to do with this: Alias it to to_field. There is no "to_field" in the list of database fields.
rh16
3,781 Pointsthis is the closest I can get to making this work: SELECT first_name || " " || last_name AS "Full Name", "<email>" AS "to_field" FROM patrons;
output:
SELECT first_name || " " || last_name AS "Full Name", "<email>" AS "to_field" FROM patrons;
Full Name to_field Andrew Chalkley <email> Dave McFarland <email> Alena Holligan <email> Michael Poley <email>
How do you get the email address to display with <> around it?
5 Answers
Robert Anthony
19,952 PointsYou have too many ||. Remove the last one before the AS
rh16
3,781 PointsSELECT first_name || " " || last_name || " <" || email || ">" email AS to_field FROM patrons;
SELECT first_name || " " || last_name || " <" || email || ">", email AS to_field FROM patrons;
first_name || " " || last_name || " <" || email || ">" to_field Andrew Chalkley andrew@teamtreehouse.com andrew@teamtreehouse.com Dave McFarland dave@teamtreehouse.com dave@teamtreehouse.com Alena Holligan alena@teamtreehouse.com alena@teamtreehouse.com Michael Poley michael@teamtreehouse.com michael@teamtreehouse.com
I finally got it to work. Thank you I tried to attach a screen shot, but this system won't let me.
SELECT first_name || " " || last_name || " <" || email || ">" AS "to_field" FROM patrons;
to_field Andrew Chalkley andrew@teamtreehouse.com Dave McFarland dave@teamtreehouse.com Alena Holligan alena@teamtreehouse.com Michael Poley michael@teamtreehouse.com
Thank you again!
rh16
3,781 PointsI voted your last answer as the best, but I'm not sure I did it right. Please let me know if it doesn't show up.
Robert Anthony
19,952 PointsWhen you alias something, it is best if it does NOT already exist in the database fields as this would cause confusion. So the end of your SQL should be
AS to_field
Also you need to concatenate the first_name, last_name AND the email with spaces between them. So far you have only done the first_name and last_name.
rh16
3,781 PointsDo you mean a space before email? I put the alias at the end, but that still doesn't give me the email address surrounded by <>. I tried putting the space before and after the email column and received syntax errors. I still can't figure out how to get the email address surrounded by <> as the exercise states.
Your answer helped some, but I still can't get all of the way there. I've been working on this for hours. Taking a break and will check for answers later.
Thanks for your help.
Robert Anthony
19,952 PointsSELECT first_name || " " || last_name || " <" || email || ">" AS to_field FROM patrons;
Concatenate the first_name to the last_name with a space between them then add a space and the <, then the email and finally the >. Alias this all as "to_field"
rh16
3,781 PointsI guess I don't understand what you are saying.. I tried this and it did not work. SELECT first_name || " " || last_name AS "Full Name" || " " < || email || " " > || FROM patrons AS "to_field";
I get a syntax error. Thanks for your help, but I am obviously not understanding something. I will keep trying. Thanks for your help.
Robert Anthony
19,952 PointsThe answer is what I wrote in the last answer!
rh16
3,781 PointsSorry, I still don't understand what you are saying. I tried what you suggested and received syntax errors. You can see what I tried in my last comment. Can you please show me a code sample of what you mean? I cannot get it to work. I have spent hours on this and come close, but just can;t get the correct result.
Thank you!
This is what I don't understand; the alias at the end worked.fine, but I can't get the email address to format properly surrounded by "<>".
Thanks for your patience, I have not used SQL Lite before, so it's s bit tricky for me,
Thanks again! I will try again and let you know if it still doesn't work. Thanks for your patience, I tried what you suggested above and get a syntax error. This is what I did and it does not work:
SELECT first_name || " " || last_name || " <" || email || ">" || AS "to_field" FROM patrons;
SQL Error: near "AS": syntax error 4/11/2018 - I just tried this again exactly as you typed it and I still get this error: SQL Error: near "AS": syntax error Any other ideas? This is really frustrating!
Rebecca
Robert Anthony
19,952 PointsTry without the quotes around the AS to_field bit.
rh16
3,781 PointsI still get the same error - SQL Error: near "AS": syntax error
rh16
3,781 Pointsrh16
3,781 PointsEven though I said to attach my code, it didn't, so you can't see what I was trying to do. I have no problem concatenating the first and last name, I also gave that an alias of "Full Name" which works right in the sandbox. It's when I try to concatenate the email and do the Alias that it fails every time. I think it's a bad question because it references "Alias to_field", but that is not in the list of fields in the table.
This works in the sandbox, but I can't get past it in the challenge question:
SELECT first_name || " " || last_name AS "Full Name"
It would be nice if you could see the database while trying to write the query, but the database in the quiz is not the same as the one in the sandbox. Any help would be appreciated