[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

postgresql join multiple tablea

Posted on 2011-10-14
5
Medium Priority
?
400 Views
Last Modified: 2012-05-12
Hi experts, i have this 3 tables:


ORDERS

order_id  |  customer
--------------------------
       1         someone


ORDERS LINES

order_id | lines_id | product | price
-----------------------------------------
       1              1       ipud          10
       1              2       ipud2        15
       1              3       ipud          10



ORDERS LINES VARIATIONS

order_id | lines_id | variation | price
-------------------------------------------
       1              2          blue         2
       1              2          darkbl     3

i would like create a view that show this result:

ORDERS VIEW

order_id | customer  | tot_price | tot_variation
-------------------------------------------------------
      1        someone        35                5        


i've try this, but with no luck:

SELECT
      orders.order_id,
      orders.customers,
      SUM(order_lines.price)AS tot_price,
      SUM(order_lines_variation.price)AS tot_variation
FROM
      (
            (
                  orders
                  LEFT JOIN orders_lines ON(
                        (
                              orders.order_id = orders_lines.order_id
                        )
                  )
            )
            LEFT JOIN ordini_righe_variazioni ON(
                  (
                        order_lines.lines_id = order_lines_variationi.lines_id
                  )
            )
      )

GROUP BY
      orders.order_id,
      orders.customers

here is the result:


order_id | customer  | tot_price | tot_variation
-------------------------------------------------------
      1        someone        50                5        

it adds the total of line_order 2 once more (35+15)

thanks to all
0
Comment
Question by:wakatanka
  • 3
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 36968154
try

select       order_id,
      customer,
      (select sum(price) from ordini_righe_variazioni where order_id = a.order_id) as tot_price,
      (select sum(price) from order_lines_variationi where order_id = a.oder_id) as tot_variation
from orders as a
0
 
LVL 41

Expert Comment

by:ralmada
ID: 36968468
or like this

select       order_id,
      customer,
      tot_price,
      tot_variation
from orders as a
left join (select order_id, sum(price) tot_price from ordini_righe_variazioni group by order_id) as b on a.order_id = b.order_id
left join (select order_id, sum(price) tot_variation from order_lines_variationi group by order_id)as c on a.order_id = c.order_id
0
 

Author Comment

by:wakatanka
ID: 36969838
tried both, doesn't work, thanks anyway
0
 
LVL 41

Expert Comment

by:ralmada
ID: 36969960
>>tried both, doesn't work, thanks anyway <<

Can you be more explicit? What do you mean that it doesn't work? Do you get an error? Not the result you want? If the latter, please post the result of the suggested query and explain why is not working.
0
 

Author Closing Comment

by:wakatanka
ID: 36972477
First time i tryed it doesn't works, probably my type mistake,
then i tryed again and it works flawless, thanks raimada
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question