Select query with missing rows or zero values?

Question

I'm not skilled at all in Postgres. Limitations are: I'm using someone else's database so no changes to tables can be made. Postgres 8.3.3 and no upgrading allowed. Accessing the database through pgAdmin III from Windows.

I'm running into a block that's killing me. If a value in one table doesn't exist in the associated table, I get no return at all. Table B has a numerical column (C_id) that references a numerical column (id) in Table C but is optional in Table B, so it can be 0 to indicate empty. There is no 0 record in C.id.

I want to select a complete list from Table B that replaces the numerical value with a label from Table C. This works as long as there is a matching number, but if B.C_id = 0 I get no line at all for that record. So table B has 66 rows but 11 of those have C_id = 0 so I only get 55 rows in my output.

WHERE B.C_id = C.id to match the tables seems to exclude the non-matching rows (B.C_id = 0) but if I leave it out, the results loop a 66 records table into 1300+ lines of output (multiplying the two tables' rows). My searching says COALESCE or LEFT JOIN should work, but COALESCE does not change the output. I presume because the disconnect is on 0 not NULL. I have not been able to get any statements to work with LEFT JOIN or CASE.

To complicate matters, both tables are addressed from another table. A simplified example of the structure:

Table A: (id, label, B_id)
Table B: (id, label, C_id)
Table C: (id, label)

All id fields are numbers and labels are text. All records in A.B_id contain a non-zero number, but B.C_id may contain 0. No rows exist in any table with id = 0.

I want to output to look like:

A.label, C.label

or, with B.C_ID = 0:

A.label, 'none'

"Postgres 8.3.3 and no upgrading allowed" - good luck with that. 8.3.3. isn't even the latests 8.3 version. You are missing 5 years of bugfixes in the 8.3 line. You are looking for a simple outer join if I understand everything correctly. — user330315
– user330315, Commented Jan 25, 2016 at 19:01
Uh, yeah, I pointed out that I was stuck on 8.3.3 so people wouldn't waste their time telling me to upgrade. I'll do more research on OUTER JOIN. I had seen many similar requests for how to do this, but most were solved using LEFT JOIN (which I can't get to work) or COALESCE which seems to have no effect, at least in my case. — Stuart K. Smith
– Stuart K. Smith, Commented Jan 25, 2016 at 23:16
The error I get when I try LEFT JOIN is: ERROR: invalid reference to FROM-clause entry for table "B" SQL state: 42P01 Hint: There is an entry for table "B", but it cannot be referenced from this part of the query. Character: 375 — Stuart K. Smith
– Stuart K. Smith, Commented Jan 25, 2016 at 23:19

Erwin Brandstetter · Accepted Answer · 2016-01-26 00:40:35Z

2

Use a LEFT [OUTER] JOIN between tables B and C. Then COALESCE to provide 'none' as default if no matching row in C is found.

SELECT A.label AS a_label, COALESCE(C.label, 'none') AS c_label
FROM      A
JOIN      B ON B.id = A.B_id
LEFT JOIN C ON C.id = B.C_id;

Assuming referential integrity between A and B I use an [INNER] JOIN for those.

Details for joins in the manual.

edited Jan 26, 2016 at 0:40

answered Jan 26, 2016 at 0:35

Erwin Brandstetter

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

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

1 Comment

Stuart K. Smith Over a year ago

Great! Thanks for taking the time to educate an old computer guy. I'm "LEFT JOINING" all over the place and COALESCE is working as I wanted. I'm well on my way to what I wanted. Thanks again.

Collectives™ on Stack Overflow

Select query with missing rows or zero values?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related