Click here to Skip to main content
15,905,233 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;
 
Share this answer
 

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