PostgreSQL query with two inner joins

Question

Not great with SQL queries, need a little help to get a working query with two inner joins.

Here's my tables:

posts users orgs

posts/users share a key of "user_id" which allows me to pull the posts table and have it also include the username of the user from the users table. I need to pull more data though.. I need to also include the "org_id" from the users table and then pull in the corresponding row from the orgs table.

Currently my SQL query is

SELECT p.*, u.username FROM posts p
INNER JOIN users u
ON p.user_id = u.user_id
ORDER BY p.post_id DESC;

and this correctly gets me the full list of posts stored in the posts table and includes the username.

I need it to include data from the orgs table.

So if Post #1 is created by user #2, I get that info together. How can I have it do another inner join that takes user #2 and gets the full set of data from the user's corresponding org?

Neto Costa · Accepted Answer · 2020-02-12 18:42:39Z

4

If I got it correctly, the query would be the following:

SELECT p.*, u.username,o.* FROM posts p
INNER JOIN users u
ON p.user_id = u.user_id
INNER JOIN orgs o
ON u.org_id=o.org_id
ORDER BY p.post_id DESC;

You can learn more about joins here.

edited Feb 12, 2020 at 18:42

answered Feb 12, 2020 at 18:36

Neto Costa

2622 silver badges16 bronze badges

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

Comments

sam · Accepted Answer · 2020-02-12 18:36:29Z

0

You can join any number of tables as you want in the same way. Please try now :

SELECT p.*,o.*, u.username FROM posts p
INNER JOIN users u
    ON p.user_id = u.user_id
INNER JOIN ORGS O 
    ON u.org_id = o.org_id 
ORDER BY p.post_id DESC;

More examples can be found here.

answered Feb 12, 2020 at 18:36

sam

1,9951 gold badge11 silver badges16 bronze badges

1 Comment

KleinBottleCode Over a year ago

Thaaaaank you! This is perfect.

richyen · Accepted Answer · 2020-02-12 18:37:50Z

0

You’re almost there. Just add the orgs table in another join condition:

SELECT p.*, o.*, u.username FROM posts p
INNER JOIN users u
ON p.user_id = u.user_id
INNER JOIN orgs o
ON o.org_id = u.org_id
ORDER BY p.post_id DESC;

answered Feb 12, 2020 at 18:37

richyen

10.3k4 gold badges18 silver badges36 bronze badges

1 Comment

KleinBottleCode Over a year ago

Thank you! This is what I needed!

Collectives™ on Stack Overflow

PostgreSQL query with two inner joins

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related