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?
|
Список | 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 по дате отправления: