Click here to Skip to main content
15,039,244 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables,

SQL
student:
rollno | name
1      | Abc
2      | efg
3      | hij
4      | klm

attendance:
name | date       |statuss
Abc  | 10-10-2013 | A
efg  | 10-10-2013 | A
Abc  | 11-10-2013 | A
hij  | 25-10-2013 | A


my required output is:
some query with where condition as "where date between '10-09-2013' and '13-10-2013' "

SQL
rollno| name |count
1     | Abc  | 2
2     | efg  | 1
3     | hij  | 0
4     | klm  | 0

i tried using:

SQL
SELECT p.rollno,p.name,case when s.statuss='A' then COUNT(p.rollno) else '0' end as count from attendance s right outer join student p on s.rollno=p.rollno where s.date between '10-09-2013' and '13-10-2013' group by p.rollno,p.regno,p.name,s.statuss order by p.rollno

output is:
rollno| name |count
1     | Abc  | 2
2     | efg  | 1


i want the remaining values from the student table to also be appended,i tried many ways all are in vain,pls provide me a query which provides the output,
thanks in advance.
Posted
Updated 30-Sep-13 5:44am
v2

1 solution

I found it,thank you :)
SQL
SELECT p.rollno,p.name,case when s.statuss='A' then COUNT(p.rollno) else '0' end as count
from    attendance s
        right outer join student p
            on s.rollno=p.rollno
            and s.date between '10-09-2013' and '13-10-2013'
group by p.rollno,p.regno,p.name,s.statuss
order by p.rollno;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900