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

Question

I have two tables here:

Table 1
Name   | Longname1  | Longname2
'John' | 'Johnny'   | 'Johnson'
'Kate' | 'Katie'    | 'Kathryn'

I have another table

Table2
Name     | Area1        | Area2
'John'   | 'Texas'      | 'Alabama'
'Kate'   | 'California' | 'Virginia'

Actually Table1.Longname1 and Table2.Area1 should be linked together, so as Table1.Longname2 and Table2.Area2. However it is not related in postgres.

Here is my trouble

How could I add back the relationship in the table?
If I would like to form a single query which will

a) Input is 'Johnny' (Table1.Longname1) and the output is 'Texas'(Table2.Area1)

b) Input is 'Johnson' (Table1.Longname2) and the output is 'Alabama'(Table2.Area2)

Which the query itself would link the relationship for me? Is it possible?

Thanks in advance

roman · Accepted Answer · 2013-08-28 12:49:23Z

3

You can pivot you columns into rows like this:

with cte1 as (
    select "Name", 1 as K, "Longname1" as Longname from Table1
    union all
    select "Name", 2 as K, "Longname2" as Longname from Table1
), cte2 as (
    select "Name", 1 as K, "Area1" as Area from Table2
    union all
    select "Name", 2 as K, "Area2" as Area from Table2
)
select
    c1."Name", c1.K, c1.Longname, c2.Area
from cte1 as c1
    inner join cte2 as c2 on c2."Name" = c1."Name" and c2.K = c1.K

so it'll become somehow related

see sql fiddle demo

And now you can put this query into view and do select like:

select *
from vw_Test
where Longname = 'Johnny'

see sql fiddle demo

If you have such a relation, you can also change you db schema so your data will be stored like:

Name   | Longname   | Area
'John' | 'Johnny'   | 'Texas'
'John' | 'Johnson'  | 'Alabama'
'Kate' | 'Katie'    | 'California'
'Kate' | 'Kathryn'  | 'Virginia'

edited Aug 28, 2013 at 12:49

answered Aug 28, 2013 at 12:41

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kos · Accepted Answer · 2013-08-28 12:44:35Z

This query:

select *
from Table1
inner join Table2 on Table1.Name = Table2.Name

would give you something similar to:

Name   | Longname1  | Longname2    | Area1        | Area2
'John' | 'Johnny'   | 'Johnson'    | 'Texas       | 'Alabama'
'Kate' | 'Katie'    | 'Kathryn'    | 'California' | 'Virginia'

and you can normally select what you want from it using a where clause.

(FYI, this is quite normal design - inner join over a 1-1 relation between tables.)

For your other question:

How could I add back the relationship in the table?

Assuming Table2 supplements Table1, you could do:

ALTER TABLE Table2 ADD FOREIGN KEY (name) REFERENCES Table1 (name);

Choose the direction that suits your design better (can one of them be optional?).

For this to work, name on Table1 must be unique (preferably primary key).

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related