vendredi 2 janvier 2015

Understanding multiple table join with aggregation


I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2



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


SQL Fiddle


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




Aucun commentaire:

Enregistrer un commentaire