Click here to Skip to main content
15,921,530 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I want a result where in schedule date must show all the dates after current date and attended dates before current date. But seems like there is some problem with my mysql query

select advocate_name , gov_advocate_id , p.case_no,
count(CAST(next_hearing_date AS DATE)) <= (CURDATE()) as attended,
count(CAST(next_hearing_date AS DATE)) > (CURDATE()) as scheduled
from case_record as c
inner join gov_advocate as g
on c.gov_advocate_id = g.advocate_id
right outer join proceeding as p
on p.case_no= c.case_no
where c.gov_advocate_id = 1
order by case_no;


It return 0 and 1 in comparison operator. I cannot use it in the where condition as both the conditions are opposite. Please help . Thanks in advance
Posted
Comments
Herman<T>.Instance 7-May-14 8:21am    
What is the exact problem?
pradnyaa 7-May-14 8:29am    
in attended and scheduled columns it returns 0 and 1
CHill60 7-May-14 8:42am    
What were you expecting to see in those columns?
pradnyaa 7-May-14 8:44am    
the count of the dates before and after current date. But seems like comparisn operator gives 0 and 1 as result. So is there any other way to do get the count of dates?
Herman<T>.Instance 7-May-14 9:01am    
you compare a count to a date so that is why it is returning true or false. Maybe forgot some brackets?

1 solution

Here you are comparing two date and the comparison returns true/false or 0/1 thats why you are getting 0/1.
your query should be like...

SQL
select advocate_name , gov_advocate_id , p.case_no,
--use your table to get count with your specific condition on date
(SELECT COUNT(ID) FROM YOUR_TABLE WHERE (CAST(next_hearing_date AS DATE)) <= (CURDATE())) as attended,
--use your table to get count with your specific condition on date
(SELECT COUNT(ID) FROM YOUR_TABLE WHERE (CAST(next_hearing_date AS DATE)) > (CURDATE())) as scheduled
from case_record as c
inner join gov_advocate as g
on c.gov_advocate_id = g.advocate_id
right outer join proceeding as p
on p.case_no= c.case_no
where c.gov_advocate_id = 1
order by case_no;
 
Share this answer
 
v2
Comments
pradnyaa 9-May-14 1:42am    
it shows syntax error in the second select statement. Doesnt mysql allow nested select statements??
Pratik Bhuva 14-May-14 0:19am    
Try to run this updated Query
pradnyaa 15-May-14 2:54am    
tnx pratik:) but i need a little more help... i want the count ie. attended or scheduled according to the case_no. but the problem is i cannot put case no. in where condition as we dont want user to select case_no and want all the case_no's ... please can u help

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