Postgres SQL - Join data from 3 table to select one column

Question

I have 3 tables with the following schema

Table1 - plan
id | count

Table2 - subscription
id | plan_id

Table3 - users
id | subscription_id

I have to select count from plans where its plan.id matches with subscription.plan_id matches and users.subscription_id matches with subscription.id and users.id = '5'

How can I use a JOIN here?

I tried using with no JOIN as below:

SELECT count 
    FROM users, subscription, plan 
    WHERE users.id = '5' and users.subscription_id = subscription.id and subscription.plan_id = plan.id;

score 0 · Accepted Answer · edited May 23 '17 at 12:19

0

Try to avoid implicit sql joins rather use explicit joins (e.g. inner join,left join..) in order to retain readability.

A cleaner way would be the following:

SELECT
    count
FROM
    users
INNER JOIN  subscription ON users.subscription_id = subscription.id
INNER JOIN  plan ON subscription.plan_id = plan.id
WHERE   users.id = '5'

Explicit vs implicit SQL joins

edited May 23 '17 at 12:19

Community

1
1

answered Sep 24 '16 at 07:40

1000111

13,169
2
28
37

You mean from `FROM plan`? – Siddharthan Asokan Sep 24 '16 at 07:44
yes. `...FROM table1,table2,table3...,tableN..` is another form of `CROSS JOIN` among those tables. – 1000111 Sep 24 '16 at 07:45

Postgres SQL - Join data from 3 table to select one column

1 Answers1