Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cross join-question: what to do when there's a null?

Cross join-question: what to do when there's a null?

From
Rikard Bosnjakovic
Date:
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

Re: Cross join-question: what to do when there's a null?

From
Thom Brown
Date:
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

Re: Cross join-question: what to do when there's a null?

From
"Oliveiros d'Azevedo Cristina"
Date:
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