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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Leon Starr | 2010-08-06 15:53:41 | Re: Best way to test/develop lots of nested functions? |
Previous Message | Thom Brown | 2010-08-06 15:33:52 | Re: Cross join-question: what to do when there's a null? |