Joining multiple nested queries in Postgresql

Question

I want to join information from 4 tables. Its super complicated, and I'm not a guru with subqueries. Would appreciate some help, if anyone can understand this.

I have a product table, and I want to look up the dealer (in a dealer info table) and join the results. Then I need to join the results on the product.owner, to a table called accounts (on account.name). I think I worked it out as this:

> d as (SELECT device_id,dealer_id,owner_id from products) i as (LEFT
> JOIN dealer ON public.dealer.id = d.dealer_id) JOIN  account ON
> i.owner_id = account.id;

Can someone help me structure this thing to report a complete result set with all information intact?

Edit:

SELECT pr.id, device_id, dealer_id, owner_id, pr.last_updated,model,brand FROM product pr
LEFT JOIN device dc ON pr.device_id = dc.id
LEFT JOIN dealer dlr ON dlr.id = pr.dealer_id
LEFT JOIN account accts ON accts.id = pr.owner_id

BShaps · Accepted Answer · 2018-06-23 00:10:10Z

1

This doesn't sound like a situation where you need any subqueries. It isn't clear what type of joins you want, but if you wanted to join dealer and accounts to the product table this is how you would do it. I did a SELECT * but you can pull individual columns by using alias.column

SELECT * FROM product pr
LEFT JOIN dealer dlr ON dlr.id = pr.dealer_id
LEFT JOIN account accts ON accts.name = pr.owner

answered Jun 23, 2018 at 0:10

BShaps

1,4248 silver badges18 bronze badges

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

2 Comments

Mattehicks Over a year ago

This works great, thanks alot. One issue I'm having is if I instead specify the columns I want from the product table, it wont return anything from the account table- it only works if I SELECT *. I added a comment to my code to explain. ( I also added a join to the account table).

BShaps Over a year ago

@Mattehicks You should be able to do accts.column_name to reference the accts columns.

Collectives™ on Stack Overflow

Joining multiple nested queries in Postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related