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 Querying Relational Databases Joining Table Data with SQL Review and Practice

Brendan Whiting
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Brendan Whiting
Front End Web Development Techdegree Graduate 84,735 Points
Posted by Brendan Whiting
.a{fill-rule:evenodd;}techdegree seal-36
Brendan Whiting
Front End Web Development Techdegree Graduate 84,735 Points

using the DISTINCT keyword with Task #1: "Patrons with Outstanding Loans"

I decided it would be a good idea to add the DISTINCT keyword to this query so I don't end up with duplicate people. I want to find distinct email addresses, not distinct first names, since two people might have the same first name.

It won't let me add the DISTINCT keyword to the 2nd column, only to the first column. If I want to make the email column distinct, I would have to return the columns in a different order than they asked me to.

This doesn't work:

SELECT patrons.first_name, DISTINCT patrons.email FROM patrons 
INNER JOIN loans on loans.patron_id = patrons.id WHERE loans.returned_on IS NULL;

This works but returns the columns in a different order:

SELECT DISTINCT patrons.email, patrons.first_name FROM patrons 
INNER JOIN loans on loans.patron_id = patrons.id WHERE loans.returned_on IS NULL;

How should I handle this?

2 Answers

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hi Brendan,

As you've discovered, the DISTINCT option has to come after SELECT but before anything else.

It doesn't apply to any particular column but to the row result as a whole. It removes duplicate rows rather than duplicate values within a column.

So if 2 rows have the same first name and email then they will be considered duplicates and one will be removed.

When you use DISTINCT the results will be the same whether you put email or first_name first with the exception of the column ordering.

The query you're probably looking for then is

SELECT DISTINCT first_name, email FROM patrons 
INNER JOIN loans on loans.patron_id = patrons.id WHERE returned_on IS NULL
Angel Avendaño
Angel Avendaño
5,013 Points
Angel Avendaño
Angel Avendaño
5,013 Points

Is this correct?

SELECT p.first_name, p.email FROM patrons AS p INNER JOIN loans AS l ON p.Id = l.patron_id WHERE returned_on IS NULL GROUP BY p.first_name;