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

Поиск
Список
Период
Сортировка
От Rikard Bosnjakovic
Тема Cross join-question: what to do when there's a null?
Дата
Msg-id AANLkTikukxG7avxG0R7CzhTSeAzC8X4SGK3HSGNaG7Uy@mail.gmail.com
обсуждение исходный текст
Ответы Re: Cross join-question: what to do when there's a null?  (Thom Brown <thom@linux.com>)
Список 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

В списке pgsql-novice по дате отправления:

Предыдущее
От: Joshua Tolley
Дата:
Сообщение: Re: Best way to test/develop lots of nested functions?
Следующее
От: Thom Brown
Дата:
Сообщение: Re: Cross join-question: what to do when there's a null?