CODEWITHSUNDEEP
sqldatabasepostgresql
ZeldaElf
ZeldaElf

Reputation: 333

JOIN solution on Postgres with many tables

Little issue on a Postgres database, being managed in Acqua Data Studio:

I need a solution to create a SELECT that concatenates many views into one table. There are more than 10 views. One central may have many IDs, and one ID may have many centrals. So, the main table's PK would be the central-ID thing.

One example that applies (assuming that exist only 3 tables), as follows:

VIEW1:

 central   |   ID   |    MAP
--------------------------------
    A      |   01   |    MAP1      
    A      |   02   |    MAP1
    A      |   03   |     -
    B      |   01   |    MAP3
    B      |   02   |     -
    C      |   01   |     -

VIEW2:

 central   |   ID   |    CAMEL
--------------------------------
    A      |   01   |    CAP1
    B      |   01   |    CAP1
    B      |   02   |    CAP2
    B      |   03   |    CAP3
    D      |   01   |     -

VIEW3:

 central   |   ID   |    NRRG
--------------------------------
    A      |   01   |   NRRG2
    B      |   01   |     -
    C      |   01   |     -
    D      |   05   |   NRRG1

. . .

Resulting Table:

 central   |   ID   |    MAP    |  CAMEL  |  NRRG
--------------------------------------------------
    A      |   01   |    MAP1   |   CAP1  |  NRRG2
    A      |   02   |    MAP1   |         |  
    A      |   03   |     -     |         |
    B      |   01   |    MAP3   |   CAP1  |    -
    B      |   02   |     -     |   CAP2  |
    B      |   03   |           |   CAP3  |
    C      |   01   |     -     |         |    -
    D      |   01   |           |    -    |
    D      |   05   |           |         |  NRRG1

Any central-ID that appears in any of the 10+ tables need to enter in the concatenated table.
I surely don't care about blank spaces on those columns that don't have a correspondent into the other columns... The important thing is to get, in each ID-central row every correspondent value that is present on the other tables. PS: "-" is a value!

I thought about a FULL OUTER JOIN, but whatching the references in manual I can't see a way to do it perfectly...

Thanks, fellas!

Upvotes: 0

Views: 76

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

SQL Fiddle

select central, id, map, camel, nrrg
from
    v1
    full outer join
    v2 using (central, id)
    full outer join
    v3 using (central, id)
order by central, id
;
 central | id | map  | camel | nrrg  
---------+----+------+-------+-------
 A       |  1 | MAP1 | CAP1  | NRRG2
 A       |  2 | MAP1 |       | 
 A       |  3 |      |       | 
 B       |  1 | MAP3 | CAP1  | 
 B       |  2 |      | CAP2  | 
 B       |  3 |      | CAP3  | 
 C       |  1 |      |       | 
 D       |  1 |      |       | 
 D       |  5 |      |       | NRRG1

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269773

A full outer join is extra complicated when you have composite keys. Instead, use the union all/group by method:

select central, id, max(map) as map, max(camel) as camel, max(nrrg) as nrrg
from ((select central, id, map, null as camel, null as nrrg
       from view1
      ) union all
      (select central, id, null as map, camel, null as nrrg
       from view2
      ) union all
      (select central, id, null as map, null as camel, nrrg
       from view3
      )
     ) v
group by central, id;

Upvotes: 0

Related Questions