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

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