Join two tables using common column in postgres

Question

I've got three database tables that are connected trough each other but not directly. Here are the tables:

Table one
-----------
id
rank
table_two_id

Table two
-----------
id
amount
table_three_id

Table three
-----------
id
name

So each row in table one can be directly linked to the row in the third table. I need to get some columns from table_one plus the name column from the third column.

I've tried many combinations so far, but this one seems to be the most promising so far, however I can't get it to work :

    select (select s.table_three_id 
            from table_two s 
            where s.id = r.table_two_id) as three_id, 
    table_three.name 
    from table_one r
    INNER JOIN table_three ON (three_id = table_three.id);

I get an error saying column three_id doesn't exist. How do I get the info from the third table based on key from table_one trough table_two.

None of your tables has a column named three_id ...

JimmyB
– JimmyB

2013-05-22 12:11:30 +00:00
Commented May 22, 2013 at 12:11 — JimmyB
– JimmyB, Commented May 22, 2013 at 12:11
and none of you tables is named table_three

wildplasser
– wildplasser

2013-05-22 12:15:35 +00:00
Commented May 22, 2013 at 12:15 — wildplasser
– wildplasser, Commented May 22, 2013 at 12:15

Mahmoud Gamal · Accepted Answer · 2013-05-22 12:11:04Z

1

It should be this way:

select 
 r.*,
 table_three.name 
from table_one r
INNER JOIN table_two s ON s.id           = r.table_two_id
INNER JOIN table_three ON table_three.id = s.table_three.id;

answered May 22, 2013 at 12:11

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

Collectives™ on Stack Overflow

Join two tables using common column in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related