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(dot)bosnjakovic(at)gmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice