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

Jan Lundeen
Jan Lundeen
5,886 Points
Posted by Jan Lundeen
Jan Lundeen
5,886 Points

Concatenating query - error message says emails are not in the correct format

Hi,

I was working on a query to concatenate first_name, last_name and email and add it as an alias using SQL. Here's the exact question:

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.

I typed in the following query:

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

Here's the error I got:

Bummer! Your query needs didn't retireve the emails in the correct format.

I believe the spelling of the email field is correct. I surrounded email with brackets and quote marks. I'm not sure what I'm doing wrong. Any ideas?

Thanks,

Jan

Eric Butler
Eric Butler
33,512 Points

Jan, What course is this challenge in? Please post it so others can more easily help you figure it out. Thanks!

Jan Lundeen
Jan Lundeen
5,886 Points
Jan Lundeen
Jan Lundeen
5,886 Points

Hi Eric,

Sorry! The course is Reporting with SQL. Does that help?

Jan

4 Answers

Eric Butler
Eric Butler
33,512 Points

Jan, Piggybacking off your exchange with Steven, I think your latest attempt in your comment just has one typo in it. Try:

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

and see if that solves it. You had one too many quote marks where the space and the < was.

Jan Lundeen
Jan Lundeen
5,886 Points
Jan Lundeen
Jan Lundeen
5,886 Points

Hi Eric,

That worked. Thanks!

However, I'm a little confused since I was trying to create a space between each column by surrounding the space with quotes (e.g. " "). However, before email it surrounds the bracket with a space as well as the bracket(e.g. " <"). Nevertheless, I certainly appreciate your help with this.

Jan

Eric Butler
Eric Butler
33,512 Points

Jan, We use quotes when we want to include customized text with the output -- whether it's empty space or not. Maybe if I try another example: Let's say I have a db table of my friends' names and I want to output something silly about them. I could write this:

SELECT first_name || " is a silly goose." AS silly_phrase FROM myFriends;

The double-pipe (||) is simply connecting my custom phrase, which is wrapped in quotes, to the first_name value. The first character of my quoted custom phrase happens to be an empty space character, simply because that is how the sentence makes sense - "Frank is a silly goose." instead of "Frankis a silly goose." Does that help at all? Hope so.

I also want to point out something important: if you wind up actually trying this on a real database and you're using MySQL, this is NOT the right way to concatenate things. You should instead use the CONCAT() function:

SELECT CONCAT(first_name, " is a silly goose.") AS silly_phrase FROM myFriends;

They mention it very very quickly in passing, but since I'd assume many if not most Treehouse learners will be learning on MySQL databases, it's funny they don't make a bigger point about this.

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

Do not put quotes around the field names.

You need quotes around your literal strings (such as " <"), but the field names (like first_name and email) should not have quotes around them.. They should also not be mixed in with other characters inside a string.

For example, instead of this:

||"<email>"

You might write this:

|| "<" || email || ">"
Jan Lundeen
Jan Lundeen
5,886 Points
Jan Lundeen
Jan Lundeen
5,886 Points

Hi Steven,

I tried that. I got the following error:

Bummer! There's something wrong with your SQL statement. Please review your code and try again.

Here's my query and the error message:

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

SQL Error: unrecognized token: "" AS to_field FROM patrons;"

I'm not sure if it's a problem with the alias (to_field) or something else. Any ideas?

Thanks,

Jan

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

You have a stray quote mark in this area: " "<"

Jan Lundeen
Jan Lundeen
5,886 Points
Jan Lundeen
Jan Lundeen
5,886 Points

Hi Steven,

Oops. I did. Sorry! Trying to fix it (unchecked it), but there is no way to do it.

Jan

Jan Lundeen
Jan Lundeen
5,886 Points
Jan Lundeen
Jan Lundeen
5,886 Points

I fixed it. I wanted to give credit to both of you (since Eric piggybacked on your answer), but I'm not sure how to do that.

Jan

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

There can only be one "best" answer. You just pick the one that helped the most in resolving your issue.

Jan Lundeen
Jan Lundeen
5,886 Points
Jan Lundeen
Jan Lundeen
5,886 Points

Hi Eric,

Thanks for explaining that. It looks like it's easier just to add a space in the second phrase than use " ". That way, you're less likely to add too many quotes (like I did).

At work, I've used only MS SQL, but I think more companies are moving to MySQL, so that's good to know. Thanks!

Jan

Eric Butler
Eric Butler
33,512 Points

You are welcome!