Skip site navigation (1) Skip section navigation (2)

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

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Rikard Bosnjakovic" <rikard(dot)bosnjakovic(at)gmail(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Cross join-question: what to do when there's a null?
Date: 2010-08-06 15:38:34
Message-ID: 30804214A3EA45B8AFE0EFD804C5B16C@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Leon StarrDate: 2010-08-06 15:53:41
Subject: Re: Best way to test/develop lots of nested functions?
Previous:From: Thom BrownDate: 2010-08-06 15:33:52
Subject: Re: Cross join-question: what to do when there's a null?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group