| From: | Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Cross join-question: what to do when there's a null? |
| Date: | 2010-08-06 15:26:23 |
| Message-ID: | AANLkTikukxG7avxG0R7CzhTSeAzC8X4SGK3HSGNaG7Uy@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Thom Brown | 2010-08-06 15:33:52 | Re: Cross join-question: what to do when there's a null? |
| Previous Message | Joshua Tolley | 2010-08-06 13:57:22 | Re: Best way to test/develop lots of nested functions? |