选择缺少行或零值的查询？

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.

我在Postgres一点都不熟练。限制是:我正在使用其他人的数据库,因此不能对表进行任何更改。 Postgres 8.3.3并且不允许升级。从Windows访问pgAdmin III中的数据库。

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.

我遇到了一个杀了我的街区。如果关联表中不存在一个表中的值,则根本不会返回任何值。表B具有引用表C中的数字列(id)但在表B中是可选的数字列(C_id),因此它可以是0以指示空。 C.id.中没有0记录。

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.

我想从表B中选择一个完整的列表,用表C中的标签替换数值。只要有匹配的数字,这就可以工作,但如果B.C_id = 0,那么该记录根本没有行。因此表B有66行,但其中11行有C_id = 0所以我的输出中只有55行。

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.

WHERE B.C_id = C.id匹配表似乎排除了不匹配的行(B.C_id = 0)但是如果我把它留下,结果将66个记录表循环到1300多行输出(乘以两个表的行。我的搜索说COALESCE或LEFT JOIN应该可以工作,但是COALESCE不会改变输出。我认为因为断开连接是0而不是NULL。我无法使用LEFT JOIN或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.

所有id字段都是数字,标签是文本。 A.B_id中的所有记录都包含非零数字,但B.C_id可能包含0.任何id = 0的表中都不存在任何行。

I want to output to look like:

我想输出看起来像:

A.label, C.label

or, with B.C_ID = 0:

或者,B.C_ID = 0:

A.label, 'none'

1 个解决方案

#1

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.

在表B和C之间使用LEFT [OUTER] JOIN。如果在C中找不到匹配的行,则COALESCE将'none'作为默认值。

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.

假设A和B之间的参照完整性,我使用[INNER] JOIN。

Details for joins in the manual.

手册中的连接细节。

#1