CODEWITHSUNDEEP
sqldatabasepostgresql
Winston
Winston

Reputation: 1428

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

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

  1. How could I add back the relationship in the table?

  2. 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

Upvotes: 2

Views: 2440

Answers (2)

roman
roman

Reputation: 117345

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'

Upvotes: 3

Kos
Kos

Reputation: 72241

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).

Upvotes: 0

Related Questions