CODEWITHSUNDEEP
sqlpostgresqldistinct
Seby
Seby

Reputation: 127

MAP DISTINCT VALUES BETWEEN TWO TABLES USING COMMON COLUMN POSTGRESQL

I have two tables

         Table A                        Table B 
id (pk) Name    zipcode         id (pk) Name    zipcode
1         A     100021          B1      T       400031
2         R     400031          B2      U       400031
3         S     100021          B3      W       100022
4         D     100021          B4      Z       100021
5         E     100021          B5      X       100021
6         F     400032          B6      O       400030
7         G     400030          B7      P       400030
8         H     100021          B8      Y       100021

the tables above have approximately 30 k data records in each
Need to map Table B records to table A using zipcode as the common key
such that each record from table B is mapped only once in table A

Desired output

id (pk) Name    zipcode     id (pk) Name    zipcode
1        A       100021      B4      Z     100021
2        R       400031      B1      T     400031
3        S       100021      B5      X     100021
4        D       100021      B8      Y     100021
5        E       100021     null    null    null
6        F       400032     null    null    null
7        G       400030      B6      O     400030
8        H       100021     null    null    null

CODE

SELECT              
   TableA.id, TableB.id             
FROM                
   (SELECT DISTINCT id FROM TableA) TableA              
   FULL OUTER JOIN              
   (SELECT DISTINCT id FROM TableB) TableB              
              ON TableA.pincode = TableB.pincode;

ERROR

"ERROR : invalid reference to FROM-clause entry for table Table A

LINE 1: R JOIN (select distinct id from Table A)Table A on ^ TableA.id

HINT: There is an entry for Table A, but it cannot be referenced from this part of the query"

Upvotes: 0

Views: 614

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

It looks like you want to "align" the tables. That is, the matching keys are zip codes and these are duplicated in each table. You want to match them 1-1, but there is no second key.

The solution is to create a second key, using row_number() and then use this with the full join:

SELECT a.*, b.*            
FROM (SELECT a.*,
             ROW_NUMBER() OVER (PARTITION BY zipcode ORDER BY id) as seqnum
      FROM TableA a
     ) a FULL JOIN          
     (SELECT b.*
             ROW_NUMBER() OVER (PARTITION BY zipcode ORDER BY id) as seqnum
      FROM TableB b
     ) b              
     ON a.zipcode = b.zipcode AND a.seqnum = b.seqnum;

Upvotes: 2

Zakir Hossain
Zakir Hossain

Reputation: 444

Try below code. should work... select distinct on (a.id) * from TableA a left join TableB b on (a.zipcode = b.zipcode) order by a.id

Upvotes: 0

Related Questions