So, I was trying to use left joins to get bot the total number of
corresponding rows and the number in a specific subset, but it seems I
can't do that in 9.4.6 (the changelogs after that don't suggest this has
changed).
This my small-scale test for it:
CREATE TABLE somedata (id bigserial, something text, PRIMARY KEY (id));
CREATE TABLE moredata (id bigserial, otherid bigint, status varchar,
PRIMARY KEY (id), FOREIGN KEY (otherid) REFERENCES somedata(id));
INSERT INTO somedata(something) VALUES ('Example 1'),('Example
2'),('Example 3');
INSERT INTO moredata(otherid,status)
VALUES(1,'NEW'),(3,'NEW'),(3,'OLD'),(3,'DEPRECATED');
SELECT somedata.id, somedata.something, count(md1.id), count(md2.id)
FROM somedata
LEFT JOIN moredata md1 ON (md1.otherid = somedata.id)
LEFT JOIN moredata md2 ON (md2.otherid = somedata.id AND md2.status NOT IN
('OLD', 'DEPRECATED'))
GROUP BY somedata.id, somedata.something;
What I expected the output to be is:
id | something | count | count
----+-----------+-------+-------
2 | Example 2 | 0 | 0
3 | Example 3 | 3 | 1
1 | Example 1 | 1 | 1
(3 rows)
Instead I got:
id | something | count | count
----+-----------+-------+-------
2 | Example 2 | 0 | 0
3 | Example 3 | 3 | 3
1 | Example 1 | 1 | 1
(3 rows)
Running the searches with individual joins:
SELECT somedata.id, somedata.something, count(md2.id)
FROM somedata
LEFT JOIN moredata md2 ON (md2.otherid = somedata.id AND md2.status NOT IN
('OLD', 'DEPRECATED'))
GROUP BY somedata.id, somedata.something;
id | something | count
----+-----------+-------
2 | Example 2 | 0
3 | Example 3 | 1
1 | Example 1 | 1
SELECT somedata.id, somedata.something, count(md1.id)
FROM somedata
LEFT JOIN moredata md1 ON (md1.otherid = somedata.id)
GROUP BY somedata.id, somedata.something;
id | something | count
----+-----------+-------
2 | Example 2 | 0
3 | Example 3 | 3
1 | Example 1 | 1
Am I misunderstanding something, or is some optimization messing-up my data?