I have problem with joining tables, here are example tables:
Table A: ( 30 rows)
╔════╦════════════╦═════════════╗ ║ ID ║ Name ║ Description ║ ╠════╬════════════╬═════════════╣ ║ 1 ║ Type ║ Unicode Art ║ ║ 2 ║ Header ║ Spreadsheet ║ ║ 3 ║ Auto Align ║ Off ║ ╚════╩════════════╩═════════════╝
Table B: ( 100 rows )
╔════╦════════════╦═════════════╦═════════╗ ║ ID ║ Name ║ Description ║ TableA ║ ╠════╬════════════╬═════════════╬═════════╣ ║ 1 ║ Type ║ Unicode Art ║ 1 ║ ║ 2 ║ Header ║ Spreadsheet ║ 1 ║ ║ 3 ║ Auto Align ║ Off ║ 2 ║ ╚════╩════════════╩═════════════╩═════════╝
Table C: ( 8000 rows )
╔════╦════════════╦═════════════╦═════════╗ ║ ID ║ Article ║ Text ║ TableB ║ ╠════╬════════════╬═════════════╬═════════╣ ║ 1 ║ Type ║ Unicode Art ║ 1 ║ ║ 2 ║ Header ║ Spreadsheet ║ 1 ║ ║ 3 ║ Auto Align ║ Off ║ 2 ║ ╚════╩════════════╩═════════════╩═════════╝
Table D: ( 100 000 rows and counting )
╔════╦═══════════╦════════════╦═════════════╦═════════╗ ║ ID ║ Date ║ Clicks ║ Impressions ║ TableC ║ ╠════╬═══════════╬════════════╬═════════════╬═════════╣ ║ 1 ║ 20120814 ║ 10 ║ 3 ║ 1 ║ ║ 2 ║ 20120815 ║ 13 ║ 5 ║ 1 ║ ║ 3 ║ 20120816 ║ 15 ║ 10 ║ 2 ║ ╚════╩═══════════╩════════════╩═════════════╩═════════╝
Table E: ( 200 000 rows and counting )
╔════╦═══════════╦════════════╦═══════════╦═════════╗ ║ ID ║ Date ║ Views ║ Visitors ║ TableC ║ ╠════╬═══════════╬════════════╬═══════════╬═════════╣ ║ 1 ║ 20120814 ║ 10 ║ 3 ║ 1 ║ ║ 2 ║ 20120815 ║ 13 ║ 5 ║ 1 ║ ║ 3 ║ 20120816 ║ 15 ║ 10 ║ 2 ║ ║ 4 ║ 20120817 ║ 8 ║ 7 ║ 2 ║ ║ 5 ║ 20120818 ║ 9 ║ 4 ║ 2 ║ ╚════╩═══════════╩════════════╩═══════════╩═════════╝
I query this tables with single sql statement:
SELECT
A.name,
A.Description,
SUM(D.clicks),
SUM(D.Impressions),
SUM(E.Views),
SUM(E.Visitors)
FROM
A
LEFT JOIN B
ON A.ID=B.TableA
LEFT JOIN C
ON B.ID=C.TableB
LEFT JOIN D
ON C.ID=D.TableC
LEFT JOIN E
ON C.ID=E.TableC
GROUP BY
A.ID
Problem is that the query returns invalid SUM for Table D and Table E
However if query Table D and Table E in invidual queries I get right values:
SELECT
A.name,
A.Description,
SUM(D.clicks),
SUM(D.Impressions)
FROM
A
LEFT JOIN B
ON A.ID=B.TableA
LEFT JOIN C
ON B.ID=C.TableB
LEFT JOIN D
ON C.ID=D.TableC
GROUP BY
A.ID
EDIT 1:
I have tried RIGHT JOIN, JOIN, LEFT OUTER JOIN none of them worked,
ofcourse it's possible that I used those in wrong places.
But in the first statement where I got "all included" values are multiplied
many thousand times higher than they really are.