I have a basic question on how
JOIN works on multiple tables. I want to count occurrences of Foreign Key in
CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); CREATE TABLE link1 ( id SERIAL PRIMARY KEY, main_id integer NOT NULL, CONSTRAINT main_id_fk FOREIGN KEY (main_id) REFERENCES main (id) ); -- link2 is similar to link1
Why does the query below give a product of counts (rather than sum) when the count is non-zero in both columns.
SELECT main.id, COUNT(link1.main_id) + COUNT(link2.main_id) FROM main LEFT JOIN link1 ON main.id=link1.main_id LEFT JOIN link2 ON main.id=link2.main_id GROUP BY main.id