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

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) ;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With