Dear all,

I have 3 tables and all the 3 tables are joined and created a command objet in crystal reports

Following are the tables and the command code.

My expected op is mentioned at the last.

Pls help me to create a report.


table a1

empno ename

1    a
2    b
3    c

table a2

empno dob

1    1
2    2
3    3

table a3

dob djoin

1    20130101
1    20130201
1    20130301
2    20140101
2    20140201
2    20140301
3    20150101
3    20150201
3    20150301

SELECT     dbo.A1.empno, dbo.A1.ename, dbo.A2.dob, dbo.A3.djoin
FROM         dbo.A3 RIGHT OUTER JOIN
                      dbo.A2 ON dbo.A3.dob = dbo.A2.dob RIGHT OUTER JOIN
                      dbo.A1 ON dbo.A2.empno = dbo.A1.empno

empno ename dob djoin

1    a    1    20130101
1    a    1    20130201
1    a    1    20130301
2    b    2    20140101
2    b    2    20140201
2    b    2    20140301
3    c    3    20150101
3    c    3    20150201
3    c    3    20150301



ip parameter is djoin
say 20130101 to 20130101
i want the following op

empno        empname        count
1        a        1
2        b        0
3        c        0

Thanks
Nick


Edited by nicklibee - 26 Jun 2013 at 11:35am