Home Joining three tables with primary in the middle
Reply: 0

Joining three tables with primary in the middle

user1885
1#
user1885 Published in April 21, 2018, 3:23 pm

I am breaking my head on joining of three tables. I have recreated a simple test case where I see the same problem, so it looks I make a fundamental mistake in my join query:

I have three tables:

case:
id (PK)| date_closed
155    | '2018-04-17 10:08' 
156    | '2018-03-17 10:08'     
pizza  | '2018-02-17 10:08' 

registration:
id (FK) | source | quantity
155     | market | 300
155     | sawdust| 200

bagged:
id | case_id (FK) | kg_bagged
X  | 155          | 123
Y  | 155          | 90

These tables I want to join to compare the total amounts per 'case' in quantity column and kg_bagged. So the case table has a 1:* many relationship to the other two. Therefore I make a join query like this:

SELECT case.id, 
       date_closed,
       SUM(quantity),
       SUM(kg_bagged),
       SUM(kg_bagged)/SUM(quantity) AS reduction_factor
FROM case
    JOIN bagged ON case.id = bagged.case_id
    JOIN registration ON case.id = registration.id

Than I would think this would be a correct query, but Postgres tells me I have to add case.id, date_closed to the group by clause. So I add this:

GROUP BY case.id, date_closed;

This code is running without errors, but it shows 1000 for the quanity at case 155 not the expected 500 (200+300). This behaviour only appears when there is more than 1 record. When joining only 1 table to the case table it also works fine. Can someone see the mistake made at the JOIN query?

I also tried using a subquery for joining two tables and than use a join on the table left, but it gave me similar results

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.298438 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO