Postgres multiple joins

Question

This is a Postgres database. I am attempting to pull dog breed names (Cane Corso, Labrador, etc) from a breed table, to display based on the foreign keys located in an animal table. My issue is the animal table has two foreign keys to this single breed table, and I keep getting errors with my query. The first breed name will return based on a left join, but the second I cannot get the name to display as I already have a left join. Below is a simplified outline of what I am attempting to do:

breed table (ID, BreedName)
animal table (ID, breedID, breed2ID)

SELECT animal.ID, breed.BreedName
FROM animal
LEFT JOIN breed ON animal.breedID=breed.ID
WHERE animal.ID='7';

What I need to do is also get the BreedName to join for animal.breed2ID which I am failing miserably at. I could easily hardcode the breed names and have them displayed in the application, but this is not conducive to changes, additions, or deletions of breed names in the database.

kingdaemon · Accepted Answer · 2012-01-08 21:22:32Z

140

just do another join on that same table:

SELECT animal.ID, breed1.BreedName as BreedName1, breed2.BreadName as BreadName2 
FROM animal 
   LEFT JOIN breed as breed1 ON animal.breedID=breed1.ID 
   LEFT JOIN breed as breed2 ON animal.breedID=breed2.ID 
WHERE animal.ID='7';

edited Jan 8, 2012 at 21:22

user330315

answered Jan 8, 2012 at 18:26

kingdaemon

1,6961 gold badge12 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1137376 Over a year ago

Thank you VERY much. That clarified and worked exactly as planned! I was messing it up by just doing a breed.AnimalName AS breedName1, breed.AnimalName AS breedName2. Completely missed the aliasing of the table! Thanks again, this is some of the best help I have seen on tech sites!

kingdaemon Over a year ago

Hey no problem, thats why we are here:D I also agree with StarShip3000's post. But i assumed that dogs with 3 or more breeds wont happen. But if they do, then be sure to take the recommended aproach by Star.

Andrew Scott Evans Over a year ago

Make sure to use explain analyze to make sure you cannot get better speed from a natural join. You would be surprised.

Kuberchaun · Accepted Answer · 2012-01-08 22:50:03Z

27

While Ivan has solved your issue for your current database design a long term consideration or just a thing to learn from would be to make your table design more normalized so that having to add a new join every time you want to add a breed to an animal is not necessary. Through this simple design you have actually made your life more difficult.

When you see repeated property types on an entity, in your case breedID and breed2ID you should typically start to smell something rotten in all but a very few rare cases.

Under the ideal design you would do the following.

1.Keep your breed as is.

2.Make animal look like something like animal(animal_id,animal_name).

3.Add a new animal_breed table. It would look like this animal_breed(animal_breed_id,animal_id,breed_id). With animal_bread_id being a pk and a unique key on (animal_id,breed_id) with foreign keys pointing back to the respective tables.

This design allows a given animal to take on one or more breed types with out ever having to mess with your query to return the multiple breeds back. Your current design becomes a nightmare every time you have an animal with an extra breed. It has the potential to kill performance and make maintenance a nightmare and on top of it just isn't sound database design.

answered Jan 8, 2012 at 22:50

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

2 Comments

Jon Vogel Over a year ago

What are the rare cases where the schema design in question is the correct choice?

Kuberchaun Over a year ago

In my experience it would be if you hit some kind of performance wall, or you have enough experience to know the use case you're working on will need this type of design to handle the processing and data volume you expect.

Nick · Accepted Answer · 2023-09-27 12:26:55Z

To avoid multiple joins, execute a single join with multiple conditionals:

SELECT a.ID, b.ID, b.BreedName
FROM animal a
JOIN breed b
ON b.ID = a.breedID OR b.ID = a.breed2ID
WHERE a.ID='7';

Why JOIN instead of LEFT JOIN?

Assuming you don’t need all records from the animal table (with or without a match), you can just use the default (inner) join instead of a left join to retrieve only matching rows.

Are there any other solutions?

Using IN instead of OR: JOIN breed ON b.id IN (a.breedID, a.breed2ID) - considering there are only 2 ids, it may be negligible, but you would need to check the execution plan to see which one is faster.

Any other notes?

Seeing how there are no constraints on these 2 foreign keys (breedID & breed2ID), there could be duplicate breed results. If you want unique breed records, you could use a GROUP BY or DISTINCT.

Collectives™ on Stack Overflow

Postgres multiple joins

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related