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

Jeremy Pzygode
Jeremy Pzygode
4,499 Points
Posted by Jeremy Pzygode
Jeremy Pzygode
4,499 Points

Reporting with SQL Challenge Task 1 out of 2

This is the challenge:

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.

This is what I input:

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

This is the error: Bummer! You're missing the angle brackets around the email address.

I have no clue how to add angle brackets. This wasn't taught in the lesson.

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hi Jeremy,

Can you link to the challenge you're on?

7 Answers

Jack Harrison-Sherlock
Jack Harrison-Sherlock
6,002 Points
Jack Harrison-Sherlock
Jack Harrison-Sherlock
6,002 Points

Try this: 

SELECT first_name || " " || last_name || " <" || email || ">" 
AS "to_field" 
FROM patrons;
Renaud Granier
Renaud Granier
5,420 Points

I had the same problem, this answer solved it. It is the good answer. Thx Sherlock! ;-)

Aaren Isabel
Aaren Isabel
6,842 Points

Thanks, Jack. What in the world do the greater than/ lesss than signs mean?

Derek Gannon
PLUS
Derek Gannon
Courses Plus Student 1,140 Points
Derek Gannon
Derek Gannon
Courses Plus Student 1,140 Points

Thanks for this. Thought I was screwing it up!

Nemat Rahimov
PLUS
Nemat Rahimov
Courses Plus Student 1,043 Points
Nemat Rahimov
Nemat Rahimov
Courses Plus Student 1,043 Points

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

Avi Tsipshtein
Avi Tsipshtein
7,461 Points
Avi Tsipshtein
Avi Tsipshtein
7,461 Points

Hi Andrew,

The issue in challenge 1 out of 2 (concateantaion in sql) is not solved yet. I even tried to replace the < and > symbols with their HTML code like this:

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

> select last_name || ' ' || first_name || " &#60;" || email || "&#62;" as to_field from patrons;

to_field
Chalkley Andrew <andrew.chalkley@teamtreehouse.com>
McFarland Dave <dave.mcfarland@teamtreehouse.com>
Holligan Alena <alena.holligan@teamtreehouse.com>
Poley Michael <michael.poley@teamtreehouse.com>

looks like the output is correct but I still get an error.

Thanks,

Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher
Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

Yep. Please use a less than sign < and a greater than sign >

Amir Cisija
Amir Cisija
2,901 Points
Amir Cisija
Amir Cisija
2,901 Points

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

It doesn't work for me, can someone tell me what is wrong?

Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher
Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

Are you still having an issue? What error are you seeing?

Maryam Naz
Maryam Naz
3,636 Points
Maryam Naz
Maryam Naz
3,636 Points

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

Ken Alger
STAFF
Ken Alger
Treehouse Teacher
Ken Alger
Ken Alger
Treehouse Teacher

Jeremy;

It sounds like you are wanting to concatenate characters together in a result. The Adding Text Columns Together video introduces us to the concatenation operator ||, or double pipes. On Windows keyboards, the pipe character is typically Shift + \.

You can concatenate characters into your field results in addition to simple things like spaces to impact formatting by including it inside quotes during the concatenation process. For example, to include a space between two field you could use first_field || ' ' || second_field.

Hope that provides some direction, but if you are still stuck, please post back.

Happy coding,
Ken

Jeremy Pzygode
Jeremy Pzygode
4,499 Points
Jeremy Pzygode
Jeremy Pzygode
4,499 Points

I updated the question. Still don't know how to do it. I've rewatched the lesson multiple times to no avail.

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

As Ken mentioned, you can concatenate whatever characters you need.

With the sql you've posted, you're concatenating only a space between the last name and the email:

last_name || " " || email

Based on the required format, there needs to be a space and then a left angle bracket. So you only need to add that to the string.

Like this:

last_name || " <" || email

So after the last name, there will be a space, then a left angle bracket followed by the email address.

Then you would need to concatenate a right angle bracket after the email to complete the required formatting.

Jeremy Pzygode
Jeremy Pzygode
4,499 Points
Jeremy Pzygode
Jeremy Pzygode
4,499 Points

Hey Jason,

I tried what you suggested and typed:

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

This still didn't work for me, I go this message:

SQL Error: near "AS": syntax error

Am I missing something?

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

You have an extra set of double pipes right before the AS keyword which you can remove since you're not concatenating anything else.

Also, I would remove the space that you have after the right angle bracket and only concatenate the right angle bracket. This way you don't have a trailing space at the end of this. I'm not sure either if it would pass the challenge with that trailing space.

Jeremy Pzygode
Jeremy Pzygode
4,499 Points
Jeremy Pzygode
Jeremy Pzygode
4,499 Points

Alright so I fixed what you said and I passed the challenge:

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

This is what was generated:

to_field
Andrew Chalkley
Dave McFarland
Alena Holligan
Michael Poley

Do you know why the <email> portion is missing from the run results? I thought it was a requirement for it to show in the run results.

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Congrats on passing the challenge.

I don't know why it's missing but I think it should be there.

Do you have a link to the challenge?

Maybe Andrew Chalkley knows why.

Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher
Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

Our console output (since it's HTML) doesn't correctly escape the less than and greater than symbols. I've opened a ticket and our development team should be able to take a look and fix it. Sorry for the confusion this causes.