mardi 30 décembre 2014

Understanding multiple table join with aggregation


I have a basic question on how JOIN works on multiple tables. Consider the tables below where I want to count the occurrences of Foreign Key in link1 and 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)
);

CREATE TABLE link2 (
id SERIAL PRIMARY KEY,
main_id integer NOT NULL,
CONSTRAINT main_id_fk FOREIGN KEY (main_id) REFERENCES main (id)
);

INSERT INTO main (name) VALUES ('a'), ('b'), ('c'), ('d');
INSERT INTO link1 (main_id) VALUES (1), (1), (2);
INSERT INTO link2 (main_id) VALUES (2), (2), (3);


SQL Fiddle


If it was just 1 table, I'd write:



SELECT main.id, COUNT(link1.main_id)
FROM main
LEFT JOIN link1 ON main.id=link1.main_id
GROUP BY main.id


With 2 tables, I extend it this way. This gives wrong results but I can't quite explain the inner working of multiple table join. I expected this to join link1 & link2 to main and add the occurrences of FK main_id in each. But it looks like the query is multiplying the results of individual counts.



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


P.S. I know how I can achieve the right answer, but I want to understand what's wrong with the above approach.





Aucun commentaire:

Enregistrer un commentaire