How to do correct Inner Join...

Hello there,

I am having a scenario where I have to use 2 inner joins to print the correct output. Here is the scenario :

Table1
C1 C2 C3 C4
1 X A a
2 Y B b
3 Z C c

Table2
C1 C2 C5
1 X x
2 Y y
3 Z z

Table3
C1 C2 C6
1 X a
2 Y b
3 X c
4 X d
5 Y e

I should get output like this:

C2 C3 C4 C5 COUNT
---- ---- ---- ----
X A a x 3
Y B b y 2
Z C c z 0

I tried left, right and full joins

Code:

---

SELECT T1.C2, T1.C3, T1.C4, T2.C5, COUNT = COUNT(*)
FROM @Table2 T2
INNER JOIN @Table1 T1 ON T2.C2 = T1.C2
LEFT JOIN @Table3 T3 ON T2.C2 = T3.C2
GROUP BY T1.C2, T1.C3, T1.C4, T2.C5

---

LEFT and FULL JOINs print

C2 C3 C4 C5 COUNT
---- ---- ---- ----
X A a x 3
Y B b y 2
Z C c z 1

RIGHT JOIN prints

C2 C3 C4 C5 COUNT
---- ---- ---- ----
X A a x 3
Y B b y 2


Please tell me the correct way to use Join

Thanks

Srinivas