CODEWITHSUNDEEP
sqlpostgresqljoin
jhamm
jhamm

Reputation: 25022

PostgreSQL - Joining 3 tables returning too many results

I have 3 tables:

Advancements
  id

Advancement_Requirements
  advancement_id,
  requirement_id

Requirements
  id
  description

I want to get all of the requirments.descriptions for a certain advancement.id. I am able to do this, but I have to use DISTINCT, which I do not want to do. If I don't use DISTINCT, I get pages and pages of results. Here is the query that I am running that works. Please tell me how to get my results without DISTINCT.

select distinct(requirements.description)
from requirements 
   inner join advancement_requirements on requirements.requirement_id = advancement_requirements.requirement_id 
   inner join advancements on advancement_requirements.advancement_id = 1;

What am I doing wrong?

Upvotes: 0

Views: 106

Answers (3)

user2253368
user2253368

Reputation: 3

You are not associating the Advancements table in your join so it just return a result for each requirement times a Advancements register.

select requirements.description from requirements inner join advancement_requirements on requirements.requirement_id = advancement_requirements.requirement_id inner join advancements on advancement_requirements.advancement_id = advancements.id
where advancements.id = 1;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656251

If you have foreign keys and a UNIQUE (or PRIMARY KEY) constraint on (advancement_id, requirement_id) in Advancement_Requirements (the typical layout for such an n:m relationship), then this simple query would not need DISTINCT.

SELECT r.description
FROM   requirements r
JOIN   advancement_requirements ar USING (requirement_id)
WHERE  ar.advancement_id = 1;

Or there is information missing in your question.

Upvotes: 0

user3507410
user3507410

Reputation: 66

I'm not sure what your are trying to accomplish but my guess is that you try to join tables and you have a search criteria. So you should do something like this.

select 
requirements.description
from requirements 
inner join advancement_requirements on requirements.requirement_id = advancement_requirements.requirement_id 
inner join advancements on advancement_requirements.advancement_id = advancements.id
WHERE advancements.id = 1;

Upvotes: 1

Related Questions