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