I have 3 tables dogs, breeds and entries. I need to get AVG(score) from entries, Name from dogs and name from breeds.
I have managed to connect 2 tables together and I’m struggle to get the last thing (breeds.name
) attached to the table
SELECT AVG(score), dogs.name, dogs.breed_id
FROM entries
JOIN dogs
ON entries.dog_id = dogs.id
JOIN breeds
ON breeds.id = dogs.breed_id
GROUP BY breeds.name, dogs.name, dogs.breed_id
having count(entries.dog_id) > 1
order by AVG(score) DESC
LIMIT 10
current result:
How do I change breed_id
for breed.name
instead?
2
Answers
Considering that you need name from breeds, name from dogs, and AVG(score) from entries, the following SQL should do that.
You have answered your own question within the question. All you need to do is replace
dogs.breed_id
withbreeds.name
. As this will leave you with two columns namedname
in your result set you should alias them to make it clearer.Assuming that the id columns are defined as PRIMARY KEYs you can GROUP BY dogs.id (or entries.dog_id) as both of the non-aggregated columns (dogs.name and breeds.name) are functionally dependent on dogs.id.