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);
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