How to join information from 3 tables into 1 column?-CodePudding

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?

CodePudding user response：

Considering that you need name from breeds, name from dogs, and AVG(score) from entries, the following SQL should do that.

SELECT breeds.name, dogs.name, AVG(score)
FROM entries  
JOIN dogs ON entries.dog_id = dogs.id
JOIN breeds ON breeds.id = dogs.breed_id
GROUP BY breeds.name, dogs.name
order by AVG(score) DESC 
LIMIT 10

CodePudding user response：

You have answered your own question within the question. All you need to do is replace dogs.breed_id with breeds.name. As this will leave you with two columns named name 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.

SELECT AVG(e.score) avg_score, d.name dog_name, b.name breed_name
FROM entries e
JOIN dogs d ON e.dog_id = d.id
JOIN breeds b ON b.id = d.breed_id
GROUP BY d.id
HAVING COUNT(e.dog_id) > 1
ORDER BY avg_score DESC 
LIMIT 10