CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.1
Ragavan Thiru
Ragavan Thiru

Reputation: 89

Postgresql relations joining

I have two relations relation1 and relation2 where relation1 has columns A,B,C and relation2 has columns D,E,F. For a query I am doing a full join on relation1 and relation2 on C=F. 

SELECT A,B,C,D,E,F 
  FROM relation1 r1 FULL JOIN relation r2 
    ON r1.C=r2.F

So when C=F, result contains 

A,B,C,D,E,F values

when C is not in F result contains 

A,B,C, null,null,null

when F is not in C result contains 

null,null,null,D,E,F

This is what i expect. Now i want to do C,F to be identified by the same name (M) in results. So when C=F, result contains 

A,B,M,D,E values

when C is not in F result contains 

A,B,M, null,null,null

when F is not in C result contains 

null,null,null,D,E,M

How to get this?

Upvotes: 1

Views: 91

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

There is no way to have M (as name) in the SELECT list twice. Output column names have to be unique.

Generally I would prefer @peterm's solution with COALESCE() (like I answered to your last question).
Here is another way to achieve the same with subqueries and the USING clause for the JOIN condition.

SELECT A, B, D, E, M 
FROM      (SELECT A, B, C AS M FROM relation1) r1
FULL JOIN (SELECT D, E, F AS M FROM relation2) r2 USING (M)

Upvotes: 0

hanzpk
hanzpk

Reputation: 123

SELECT A,B,
  case when C is not null then M end,
  D,E,
  case when F is not null then M end
FROM relation1 r1 
  FULL JOIN relation r2 
ON r1.C=r2.F

unless M is just an alias then 

SELECT A,B,C as M, D,E, F as M
FROM relation1 r1 
  FULL JOIN relation r2 
ON r1.C=r2.F

Upvotes: 0

peterm
peterm

Reputation: 92805

Are you looking for this?

SELECT A, B, D, E, COALESCE(C, F) M 
  FROM relation1 r1 FULL JOIN relation2 r2 
    ON r1.C = r2.F

Assuming relation1:

|  A |  B | C |
---------------
| a1 | b1 | 1 |
| a2 | b2 | 2 |

and relation2:

|  D |  E | F |
---------------
| d1 | e1 | 1 |
| d3 | e3 | 3 |

Output will be

|      A |      B |      D |      E | M |
-----------------------------------------
|     a1 |     b1 |     d1 |     e1 | 1 |
|     a2 |     b2 | (null) | (null) | 2 |
| (null) | (null) |     d3 |     e3 | 3 |

Here is SQLFiddle demo

Upvotes: 1

VB.NET LEARNER
VB.NET LEARNER

Reputation: 711

You can use alias i.e C as M and F as M in your query

Upvotes: 0

Related Questions