sql - Postgres: How to form a query with 2 unrelated table column? -

i have 2 tables here:

table 1 name   | longname1  | longname2 'john' | 'johnny'   | 'johnson' 'kate' | 'katie'    | 'kathryn' 

i have table

table2 name     | area1        | area2 'john'   | 'texas'      | 'alabama' 'kate'   | 'california' | 'virginia' 

actually table1.longname1 , table2.area1 should linked together, table1.longname2 , table2.area2. not related in postgres.

here trouble

1. how add relationship in table?

2. if form single query will

   a) input 'johnny' (table1.longname1) , output 'texas'(table2.area1)

   b) input 'johnson' (table1.longname2) , output 'alabama'(table2.area2)

   which query link relationship me? possible?

thanks in advance

you can pivot columns rows this:

with cte1 (     select "name", 1 k, "longname1" longname table1     union     select "name", 2 k, "longname2" longname table1 ), cte2 (     select "name", 1 k, "area1" area table2     union     select "name", 2 k, "area2" area table2 ) select     c1."name", c1.k, c1.longname, c2.area cte1 c1     inner join cte2 c2 on c2."name" = c1."name" , c2.k = c1.k 

so it'll become somehow related

see sql fiddle demo

and can put query view , select like:

select * vw_test longname = 'johnny' 

see sql fiddle demo

if have such relation, can change db schema data stored like:

name   | longname   | area 'john' | 'johnny'   | 'texas' 'john' | 'johnson'  | 'alabama' 'kate' | 'katie'    | 'california' 'kate' | 'kathryn'  | 'virginia'