Обсуждение: Cross join-question: what to do when there's a null?
Consider this small table: ecdb=> SELECT id, name, package FROM components; id | name | package ----+------+--------- 1 | 1 | 2 2 | 2 | (2 rows) The values in name and package refer to two other tables. I would like to extract the name in the same query, so I run a cross-join: ecdb=> SELECT c.id, x.name, c.package FROM components c, component_names x WHERE c.id = x.id; id | name | package ----+--------+--------- 1 | BC547 | 2 2 | 1N4148 | (2 rows) Next, I would also like the package name to be shown. However, since there's a null (which is perfectly legal in this case) I am unable to get it to work: ecdb=> SELECT c.id, x.name, y.name FROM components c, component_names x, packages y WHERE c.id = x.id AND c.package = y.id; id | name | name ----+-------+------ 1 | BC547 | SO (1 row) The result I'm looking for is this: id | name | package ----+--------+--------- 1 | BC547 | SO 2 | 1N4148 | (2 rows) How do I run a cross join like this that will include the null element? -- - Rikard
On 6 August 2010 16:26, Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> wrote: > Consider this small table: > > ecdb=> SELECT id, name, package FROM components; > id | name | package > ----+------+--------- > 1 | 1 | 2 > 2 | 2 | > (2 rows) > > The values in name and package refer to two other tables. I would like > to extract the name in the same query, so I run a cross-join: > > ecdb=> SELECT c.id, x.name, c.package FROM components c, > component_names x WHERE c.id = x.id; > id | name | package > ----+--------+--------- > 1 | BC547 | 2 > 2 | 1N4148 | > (2 rows) > > > Next, I would also like the package name to be shown. However, since > there's a null (which is perfectly legal in this case) I am unable to > get it to work: > > ecdb=> SELECT c.id, x.name, y.name FROM components c, component_names > x, packages y WHERE c.id = x.id AND c.package = y.id; > id | name | name > ----+-------+------ > 1 | BC547 | SO > (1 row) > > > The result I'm looking for is this: > > id | name | package > ----+--------+--------- > 1 | BC547 | SO > 2 | 1N4148 | > (2 rows) > > How do I run a cross join like this that will include the null element? > Would this work? SELECT c.id, x.name, y.name FROM components c INNER JOIN component_names x ON c.id = x.id LEFT JOIN packages y ON c.package = y.id; It's recommended to use JOIN clauses to join your tables as it makes it easier to understand where they join. In the case above, it will only return rows from "components" if there's a corresponding row in "component_names" because of the INNER JOIN. The LEFT JOIN basically only gets rows from "packages" if there is a matching row, but doesn't require it. -- Thom Brown Registered Linux user: #516935
Howdy! Try a Left join like this, SELECT firstQuery.id, firstQuery.name, package.name FROM ( SELECT c.id, x.name, c.package FROM components c, component_names x WHERE c.id = x.id ) firstQuery LEFT JOIN package ON firstQuery.package = package.id And tell me if it solved the problem Best, Oliver ----- Original Message ----- From: "Rikard Bosnjakovic" <rikard.bosnjakovic@gmail.com> To: <pgsql-novice@postgresql.org> Sent: Friday, August 06, 2010 4:26 PM Subject: [NOVICE] Cross join-question: what to do when there's a null? > Consider this small table: > > ecdb=> SELECT id, name, package FROM components; > id | name | package > ----+------+--------- > 1 | 1 | 2 > 2 | 2 | > (2 rows) > > The values in name and package refer to two other tables. I would like > to extract the name in the same query, so I run a cross-join: > > ecdb=> SELECT c.id, x.name, c.package FROM components c, > component_names x WHERE c.id = x.id; > id | name | package > ----+--------+--------- > 1 | BC547 | 2 > 2 | 1N4148 | > (2 rows) > > > Next, I would also like the package name to be shown. However, since > there's a null (which is perfectly legal in this case) I am unable to > get it to work: > > ecdb=> SELECT c.id, x.name, y.name FROM components c, component_names > x, packages y WHERE c.id = x.id AND c.package = y.id; > id | name | name > ----+-------+------ > 1 | BC547 | SO > (1 row) > > > The result I'm looking for is this: > > id | name | package > ----+--------+--------- > 1 | BC547 | SO > 2 | 1N4148 | > (2 rows) > > How do I run a cross join like this that will include the null element? > > > -- > - Rikard > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice