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,887 Points
Posted by Jan Lundeen
Jan Lundeen
5,887 Points

In Querying Relational Databases(challenge 5 of 5), I'm not sure why I'm getting "ambiguous column name" error

Hi,

In Querying Relational Databases(challenge 5 of 5), I'm not sure why I'm getting "ambiguous column name" error. Here's the question:

Challenge 5 of 5: In a car database there is a Sale table with columns, SaleID, CarID, CustomerID, LocationID, SalesRepID,SaleAmount and SaleDate. The database also has a SalesRep table with columns, SalesRepID, FirstName, LastName, SSN, PhoneNumber, StreetAddress, City, State and ZipCode. Show all SaleDate, SaleAmount, and SalesRep First and Last name from Sale and SalesRep. Make sure that all Sales appear in results even if there is no SalesRep associated to the sale.

Here's my query:

SELECT SaleDate, SaleAmount, SalesRepID, FirstName, LastName FROM Sale AS s LEFT OUTER JOIN SalesRep AS sr ON s.SalesRepID = sr.SalesRepID;

Here's the error:

SQL Error: ambiguous column name: SalesRepID

Since I haven't joined either the Sale or SalesRepID tables twice and I added an alias to distinguish SalesRepID in the Sale table from the SalesRepID table (e.g. s.SalesRepID and sr.SalesRepID), I'm not sure why this is happening. What is causing this error?

Thanks,

Jan

Benjamin Larson
Benjamin Larson
34,055 Points
Benjamin Larson
Benjamin Larson
34,055 Points

Can you provide a link to the specific challenge?

Benjamin Larson
Benjamin Larson
34,055 Points
Benjamin Larson
Benjamin Larson
34,055 Points

Just from looking at what you have, it seems like you don't even need to SELECT the SalesRepID. And even though you are distinguishing between them when you join the tables, that does not distinguish them when you select the columns.

5 Answers

Benjamin Larson
Benjamin Larson
34,055 Points
Benjamin Larson
Benjamin Larson
34,055 Points

Okay, I found the correct challenge and confirmed that all you need to do is remove the SELECT for the SalesRepID column. When you join on columns you don't actually need to select them. SELECT is really just for what you want to output, but the join conditions are completely separate. If you did need to select that column, you could just need to preface it with a table name as you do with the joins.

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Benjamin,

Glad you found it. Sorry, I thought that since I clicked the option to show my code when I created the question that it would show the link. Apparently, it doesn't do that. I'll provide the link in the future.

The question asks the user to show SalesRep(see below. It looks like they forgot to add the ID to SalesRep ), so I think I have to list it as a column in my output(unless I'm not reading this correctly).

"Show all SaleDate, SaleAmount, and SalesRep First and Last name from Sale and SalesRep."

Okay, that's good to know that I don't need to include the SalesRepID column in my output if I use it in my join conditions. Since the question asks for the SalesRep column, it looks like I'll need to preface it with s.SalesRepID and sr.SalesRepID. Thanks for pointing that out.

Jan

Benjamin Larson
Benjamin Larson
34,055 Points
Benjamin Larson
Benjamin Larson
34,055 Points

When it asks to show SalesRep, it is meaning the first and last name of the sales rep. I confirmed submitting the challenge with what you already have, minus the SalesRepID column and it passes.

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Benjamin,

Okay, so that's what it means. I tried taking out the SalesRepID and it passed. Thanks!

Jan

minh nguyen
minh nguyen
55,848 Points
minh nguyen
minh nguyen
55,848 Points

SELECT SaleDate, SaleAmount, FirstName, LastName FROM Sale AS s LEFT OUTER JOIN SalesRep AS sr ON s.SalesRepID = sr.SalesRepID;

Kevin Gates
Kevin Gates
15,053 Points
Kevin Gates
Kevin Gates
15,053 Points

A correct answer is:

SELECT s.SaleDate, s.SaleAmount, sr.FirstName, sr.LastName
FROM Sale AS s
   LEFT OUTER JOIN SalesRep AS sr
      ON s.SalesRepID = sr.SalesRepID;

Since they want to have sales even if there is no sales rep, that means that there are sales occurring without a sales rep.

If we're implementing a LEFT OUTER JOIN, that means the table on the left needs to always have a value returned. This means we list Sale first after the FROM keyword.

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Kevin,

Thanks for your answer. However, this is from 2017, so I already got my answer.

Jan

Tinashe Mutematsaka
Tinashe Mutematsaka
9,087 Points
Tinashe Mutematsaka
Tinashe Mutematsaka
9,087 Points

SELECT Sale.saleDate, Sale.saleAmount, SalesRep.firstName, SalesRep.lastName FROM Sale LEFT OUTER JOIN SalesRep ON Sale.salesRepId = SalesRep.salesRepId

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Tinashe,

I think I already have an answer. Thanks anyway!

Jan