CODEWITHSUNDEEP
sql
C.A. Zeppel
C.A. Zeppel

Reputation: 21

Left join & ambiguous error

I'm struggling with a (most likely) very simple problem. I keep getting the following error:

Error: ambiguous column name: Rek.name

Okay, I theoretically know what this error means means. However, I don't quite get why it appears when I try to use this:

SELECT DISTINCT Rek.name AS abc, COUNT(DISTINCT Fav.FavId) AS def
FROM Rek, Fav
LEFT JOIN Artist ON Rek.RekId = Fav.RekId;

Why is Rek.name ambiguous? How can one make it non-ambiguous?

Upvotes: 2

Views: 1166

Answers (2)

Barthelemy Pavy
Barthelemy Pavy

Reputation: 570

If you want to join Fav table :

SELECT DISTINCT Rek.name AS abc, COUNT(DISTINCT Fav.FavId) AS def
FROM Rek
LEFT JOIN Fav ON Rek.RekId = Fav.RekId
GROUP BY Rek.name;

If you want also Artist table :

SELECT DISTINCT Rek.name AS abc, COUNT(DISTINCT Fav.FavId) AS def
FROM Rek
LEFT JOIN Fav ON Rek.RekId = Fav.RekId
LEFT JOIN Artist ON Rek.common_attribute= Artist.common_attribute
GROUP BY Rek.name;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Huh? I think the query you want looks like this:

SELECT Rek.name AS abc, COUNT(Fav.RekId) AS def
FROM Rek LEFT JOIN
     Fav
     ON Rek.RekId = Fav.RekId
GROUP BY Rek.name;

Notes:

  • The Artist table is not needed.
  • SELECT DISTINCT is an awkward way of doing GROUP BY.
  • Presumably fav.favid is a primary key. You don't need COUNT(DISTINCT) in that case.

Upvotes: 1

Related Questions