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