Full outer join? Cross product? How to blend two queries into single row?

Поиск
Список
Период
Сортировка
От D. Dante Lorenso
Тема Full outer join? Cross product? How to blend two queries into single row?
Дата
Msg-id 48C04875.1000708@lorenso.com
обсуждение исходный текст
Ответы Re: Full outer join? Cross product? How to blend two queries into single row?  (Sam Mason <sam@samason.me.uk>)
Список pgsql-general
All,

I want to do something simple and the terminology is slipping me.  I
want to execute two separate queries that should return 0 or 1 rows and
I want to join the results of those queries into a single row.

   SELECT a.col1, a.col2
   FROM mytable a
   WHERE a.uid = 'abc';

   SELECT b.col3, b.col4
   FROM mytable b
   WHERE b.uid = 'def';

But somehow get a result like this:

   SELECT a.col1, a.col2, b.col3, b.col4
   FROM mytable a, mytable b
   WHERE a.uid = 'abc'
   AND b.uid = 'def';

That query works when both a.uid and b.uid match but I want to get
results even when a.uid matches but b.uid does NOT match and vice versa.
  Just make a.col1 and a.col2 NULL when a.uid does not match or make
b.col3 and b.col4 NULL when b.uid does not match.  All 4 can be NULL or
no rows returned if no matches.

I came up with this query that works, but seems overly complicated:

   SELECT a.col1, a.col2, b.col3, b.col4
   FROM
      (SELECT col1, col3, TRUE AS join_column
       FROM mytable
       WHERE uid = 'abc') a
     FULL OUTER JOIN
      (SELECT col3, col4, TRUE AS join_column
       FROM mytable
       WHERE uid = 'def') b
     ON (a.join_column = b.join_column);

Is this how to do it, or is there a simpler syntax I'm missing?  What's
the formal terminology for what I'm doing here?  Is there a way to
specify a FULL OUTER JOIN without naming the join column since I don't
need one?

-- Dante

----------
D. Dante Lorenso
dante@lorenso.com

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

Предыдущее
От: "Richard Broersma"
Дата:
Сообщение: Re: How to test something using ROLLBACK TRANSACTION
Следующее
От: "Scott Marlowe"
Дата:
Сообщение: Re: Postgres does not start, gives no error