hi recursiveNugget,
bad news! I have checked your join operations on those 6 tables, and the (wrong) results are:
1. contact.id
id 2 will be counted 20 times
id 5 will be counted 16 times
id 8 will be counted 4 times
2. Indeed, you need LEFT OUTER JOIN. With inner join (which would be the appropriate one
for such tasks, but...) you would lose all information about contact.id 2 and 8.
3. However, the result is wrong because you join four tables (tdocumentsA..D) independently
together. The information you have spread over these 4 table MUST be arranged in ONE
table only. Then applying of INNER JOIN on tusers INNER JOIN tContacts INNER JOIN
tdocuments would work properly.
Long story short, you need to redesign your tables.
krs,
tesu
Btw, below is the result of the last join, where you can check out why inner join would produce poorer results (all rows containing (NULL) would then disappear)
# DocA DocB DocC DocD
1 1 2 C2 A1 B3 (NULL) D1
1 1 2 C2 A1 B4 (NULL) D1
1 1 2 C2 A1 B6 (NULL) D1
1 1 2 C2 A1 B7 (NULL) D1
1 1 2 C2 A1 B8 (NULL) D1
1 1 2 C2 A1 B3 (NULL) D3
1 1 2 C2 A1 B4 (NULL) D3
1 1 2 C2 A1 B6 (NULL) D3
1 1 2 C2 A1 B7 (NULL) D3
1 1 2 C2 A1 B8 (NULL) D3
1 1 2 C2 A1 B3 (NULL) D4
1 1 2 C2 A1 B4 (NULL) D4
1 1 2 C2 A1 B6 (NULL) D4
1 1 2 C2 A1 B7 (NULL) D4
1 1 2 C2 A1 B8 (NULL) D4
1 1 2 C2 A1 B3 (NULL) D8
1 1 2 C2 A1 B4 (NULL) D8
1 1 2 C2 A1 B6 (NULL) D8
1 1 2 C2 A1 B7 (NULL) D8
1 1 2 C2 A1 B8 (NULL) D8
3 1 5 C5 A3 B2 C5 D5
3 1 5 C5 A4 B2 C5 D5
3 1 5 C5 A5 B2 C5 D5
3 1 5 C5 A8 B2 C5 D5
3 1 5 C5 A3 B2 C6 D5
3 1 5 C5 A4 B2 C6 D5
3 1 5 C5 A5 B2 C6 D5
3 1 5 C5 A8 B2 C6 D5
3 1 5 C5 A3 B2 C5 D5
3 1 5 C5 A4 B2 C5 D5
3 1 5 C5 A5 B2 C5 D5
3 1 5 C5 A8 B2 C5 D5
3 1 5 C5 A3 B2 C6 D6
3 1 5 C5 A4 B2 C6 D6
3 1 5 C5 A5 B2 C6 D6
3 1 5 C5 A8 B2 C6 D6
4 1 8 C8 A6 (NULL) C2 (NULL)
4 1 8 C8 A7 (NULL) C2 (NULL)
4 1 8 C8 A6 (NULL) C2 (NULL)
4 1 8 C8 A7 (NULL) C2 (NULL)