Postgres join table: Return only records from one table but with values from others [duplicate]

Question

I have a table with ~5,000 records. I have made three join columns in this table. The values in each column are not unique. I want to join to another table (sequentially) by each of these three columns to return values given a condition.

The join table contains multiple columns. Three of these columns are the join columns which will correspond to the first tables' join columns. The join columns in the join table are unique. I want to take the values from the join table and bring to a new column in the first table.

I have a code that I have put together from other suggestions and it runs but I am receiving over 8 million records in the return table. I want the table to only have the records from the first table.

Here is the code:

CREATE TABLE current_condition_joined AS SELECT
  a.id, a.geom, a.condition_join_1, a.condition_join_2, a.condition_join_3,
  coalesce(b.condition, c.condition2, d.condition3) as current_condition, 
  coalesce(b.ecosite, c.ecosite2, d.ecosite3) as current_ecosite,
  coalesce(b.ecophase, c.ecophase2, d.ecophase3) as current_ecophase,
  coalesce(b.consite, c.consite2, d.consite3) as current_consite,
  coalesce(b.conphase, c.conphase2, d.conphase3) as current_conphase
 FROM current_condition a
 LEFT JOIN boreal_mixedwood_labeled b ON a.condition_join_1 = b.label
 LEFT JOIN boreal_mixedwood_labeled c ON a.condition_join_2 = c.label2
 LEFT JOIN boreal_mixedwood_labeled d ON a.condition_join_3 = d.label3
 WHERE b.condition != 'ERROR' and c.condition2 != 'ERROR';

I want to get the values from the first join if condition is not ERROR, else the values from the second join if condition is not ERROR, else the values of the third join.

I've looked around, but all examples are asking slightly different things then I am so I can't piece it together.

This is not the same question as: Nested Case statement type error (postgres) The question asked there was in regard to making a nested statement work. This question is about how the join works. Two different questions, two different posts.

I suspect your question already has an answer under your previous question. What you present here is a broken version of the answer. Either way, without table definitions (especially for boreal_mixedwood_labeled) the question is going nowhere. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 5, 2015 at 9:50

Thiago Aleixo · Accepted Answer · 2015-12-05 03:41:23Z

0

Try add a DISTINCT.

CREATE TABLE current_condition_joined AS SELECT DISTINCT
  a.id, a.geom, a.condition_join_1, a.condition_join_2, a.condition_join_3,
  coalesce(b.condition, c.condition2, d.condition3) as current_condition, 
  coalesce(b.ecosite, c.ecosite2, d.ecosite3) as current_ecosite,
  coalesce(b.ecophase, c.ecophase2, d.ecophase3) as current_ecophase,
  coalesce(b.consite, c.consite2, d.consite3) as current_consite,
  coalesce(b.conphase, c.conphase2, d.conphase3) as current_conphase
 FROM current_condition a
 LEFT JOIN boreal_mixedwood_labeled b ON a.condition_join_1 = b.label
 LEFT JOIN boreal_mixedwood_labeled c ON a.condition_join_2 = c.label2
 LEFT JOIN boreal_mixedwood_labeled d ON a.condition_join_3 = d.label3
 WHERE b.condition != 'ERROR' and c.condition2 != 'ERROR';

You can try use GROUP BY too.

answered Dec 5, 2015 at 3:41

Thiago Aleixo

616 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:59:20Z

0

The code you present is what I gave you for your previous question:

Nested Case statement type error (postgres).

But you broke it by moving the conditions b.condition != 'ERROR' and c.condition2 != 'ERROR' to the WHERE clause, which is simply wrong. Consider:

Query with LEFT JOIN not returning rows for count of 0

If rows are multiplied, then your join conditions most probably identify multiple matching rows, multiplying each other. Hard to diagnose while you still refuse to provide the table definition of boreal_mixedwood_labeled like I requested repeatedly for your previous question.

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Dec 5, 2015 at 9:43

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

1 Comment

D_C Over a year ago

I have a co-worker that suggested this code to me at around the same time you offered it in a slightly different form. I did not think that it they were different. I will place the definition of the other table in the other question. But this should not be needed for this question.

Collectives™ on Stack Overflow

Postgres join table: Return only records from one table but with values from others [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related