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

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

From: Thom Brown <thom(at)linux(dot)com>
To: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Cross join-question: what to do when there's a null?
Date: 2010-08-06 15:33:52
Message-ID: AANLkTintay45=Y1Y6Nn7fjUoTdgAfi=s3-NOGjNtQQVP@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 6 August 2010 16:26, Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)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

In response to

pgsql-novice by date

Next:From: Oliveiros d'Azevedo CristinaDate: 2010-08-06 15:38:34
Subject: Re: Cross join-question: what to do when there's a null?
Previous:From: Rikard BosnjakovicDate: 2010-08-06 15:26:23
Subject: Cross join-question: what to do when there's a null?

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