Re: If statement in a where query....

[SGreen]

You don't need an IF only parentheses. This is almost a literal 
translation of your exact statement

Select * 
from db 
where (last_renewal_date between '2004-11-01' and '2004-11-30')
 or (last_renweal_date is null
AND signup_date between '2004-11-01' and '2004-11-30'
) 
order by last_renewal_date

Since you are checking the same beginning and ending dates twice, it makes 
sense to move those values to their own variables (keeping the variable 
names in line with your example):

SET a='2004-11-01', b='2004-11-30';

Select * 
from db 
where (last_renewal_date between a and b)
 or (last_renewal_date is null
AND signup_date between a and b
) 
order by last_renewal_date;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Mike Morton [EMAIL PROTECTED] wrote on 12/01/2004 02:47:19 PM:

 I am trying to find a list of people based on renewal dates, the logic 
of
 the query would be:
 
 Select everything from the db where the last renewal date is between a 
and
 b, or if there is no last renewal date then where the signup date is 
between
 a and b
 
 The query that I have is something like:
 
 Select * 
 from db 
 where last_renewal_date
 between '2004-11-01' and '2004-11-30' or
 if(
 length(last_renewal_date)  1,
 signup_date between '2004-11-01' and '2004-11-30'
 ) 
 order by last_renewal_date
 
 Obviously the syntax is wrong here, but is there a way to accomplish 
this
 logic using an if statement in the where statement?
 
 TIA!
 
 --
 Cheers
 
 Mike Morton
 
 
 *
 * Tel: 905-465-1263
 * Email: [EMAIL PROTECTED]
 *
 
 
 Indeed, it would not be an exaggeration to describe the history of the
 computer industry for the past decade as a massive effort to keep up 
with
 Apple.
 - Byte Magazine
 
 Given infinite time, 100 monkeys could type out the complete works of
 Shakespeare. Win 98 source code? Eight monkeys, five minutes.
 -- NullGrey 
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: If statement in a where query....

[Rhino]

- Original Message - 
From: Mike Morton [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 2:47 PM
Subject: If statement in a where query


 I am trying to find a list of people based on renewal dates, the logic of
 the query would be:

 Select everything from the db where the last renewal date is between a and
 b, or if there is no last renewal date then where the signup date is
between
 a and b

 The query that I have is something like:

 Select *
 from db
 where last_renewal_date
 between '2004-11-01' and '2004-11-30' or
 if(
 length(last_renewal_date)  1,
 signup_date between '2004-11-01' and '2004-11-30'
 )
 order by last_renewal_date

 Obviously the syntax is wrong here, but is there a way to accomplish this
 logic using an if statement in the where statement?

You don't need an 'if' to do what you want. Using 'and' and 'or' and
brackets around your conditions should be enough. Something like this should
do the trick:

Select *
from db
where last_renewal_date between '2004-11-01' and '2004-11-30'
or (length(last_renewal_date)  1 and signup_date between '2004-11-01' and
'2004-11-30')
order by last_renewal_date

That will ensure that the rows which are selected either have their last
renewal date in Nov 2004 -OR- that it is less than a year since their last
renewal and they signed up in Nov 2004.

Rhino


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: If statement in a where query....

[yoge]

Check whether below query helps u ?
Select * from db where IFNULL(last_renewal_date,signup_date) between 
'2004-11-01' and '2004-11-30'
Regards
--Yoge

Mike Morton wrote:
I am trying to find a list of people based on renewal dates, the logic of
the query would be:
Select everything from the db where the last renewal date is between a and
b, or if there is no last renewal date then where the signup date is between
a and b
The query that I have is something like:
Select * 
from db 
where last_renewal_date
   between '2004-11-01' and '2004-11-30' or
   if(
   length(last_renewal_date)  1,
   signup_date between '2004-11-01' and '2004-11-30'
   ) 
order by last_renewal_date

Obviously the syntax is wrong here, but is there a way to accomplish this
logic using an if statement in the where statement?
TIA!
--
Cheers
Mike Morton

*
* Tel: 905-465-1263
* Email: [EMAIL PROTECTED]
*

Indeed, it would not be an exaggeration to describe the history of the
computer industry for the past decade as a massive effort to keep up with
Apple.
- Byte Magazine
Given infinite time, 100 monkeys could type out the complete works of
Shakespeare. Win 98 source code? Eight monkeys, five minutes.
-- NullGrey 


 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]