mysql query not working

Question

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 7-May-14 2:11am

pradnyaa

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Pratik Bhuva · Answer 1 · 2014-05-07T03:33:00

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;