CODEWITHSUNDEEP
postgresql
user1137376
user1137376

Reputation: 843

Postgres multiple joins

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.

Upvotes: 74

Views: 137294

Answers (3)

Nick
Nick

Reputation: 21

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.

Upvotes: 2

Kuberchaun
Kuberchaun

Reputation: 30314

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.

Upvotes: 27

kingdaemon
kingdaemon

Reputation: 1686

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';

Upvotes: 139

Related Questions