Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: multiple LEFT JOIN with multiple conditions

Here is an extract of my data model (including an extract of tables content).

enter image description here

I need to compulse the number of operations of type 1 over year 2015. I also want the complete list of towns in my result, not only towns referenced in the operation table (with a number equal to zero for towns with no registered operations). I then need to specify several conditions but the WHERE clause turns my LEFT JOIN in an INNER JOIN (see this post), so I have to specify the conditions inside the ON clauses.

SELECT
  town.town_code,
  count(operation.*) AS nb

FROM town
  LEFT JOIN operation ON town.town_code = operation.ope_town AND operation.ope_year = 2015
  LEFT JOIN intervention ON operation.ope_id = intervention.int_ope_id
  LEFT JOIN nature ON intervention.int_id = nature.int_id AND nature.type_id = 1

GROUP BY town.town_code ORDER BY town.town_code ;

I get the following result:

town_code   | nb
------------+-----
86000       | 1
86001       | 0
86002       | 1
86003       | 1
86004       | 0
86005       | 0

There is a problem with town code 86003 which should have 0. This town code refers to one operation (#5) which refers to one intervention (#16) which refers to a nature type = 3. So one of the conditions is not filled...

How can I deal with several conditions within ON clauses?


EDIT : Here is the script to create the tables and test.

CREATE TABLE town (town_code INTEGER, town_name CHARACTER VARING(255)) ;
CREATE TABLE operation (ope_id INTEGER, ope_year INTEGER, ope_town INTEGER) ;
CREATE TABLE intervention (int_id INTEGER, int_ope_id INTEGER) ;
CREATE TABLE nature (int_id INTEGER, type_id INTEGER) ;

INSERT INTO town VALUES (86000, 'Lille'), (86001, 'Paris'), (86002, 'Nantes'), (86003, 'Rennes'), (86004, 'Marseille'), (86005, 'Londres') ;
INSERT INTO operation VALUES (1, 2014, 86000), (2, 2015, 86000), (3, 2012, 86001), (4, 2015, 86002), (5, 2015, 86003) ;
INSERT INTO intervention VALUES (12, 1), (13, 2), (14, 3), (15, 4), (16, 5) ;
INSERT INTO nature VALUES (12, 1), (13, 1), (14, 3), (15, 1), (16, 3) ;
like image 517
wiltomap Avatar asked Apr 10 '26 08:04

wiltomap


1 Answers

It's because you select first left join. For examle you can use:

SELECT t.town_code, count(j.*) AS nb FROM town t
  LEFT JOIN (SELECT o.ope_town cd, o.ope_year yr FROM operation o, intervention i, nature n
             WHERE o.ope_year = 2015 
             AND o.ope_id = i.int_ope_id AND n.type_id = 1 
             AND i.int_id = n.int_id) j 
             ON j.cd = t.town_code
 GROUP BY t.town_code ORDER BY t.town_code;
like image 165
Shtol Krakov Avatar answered Apr 13 '26 00:04

Shtol Krakov



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!