Struggling with OR in a query???

[tHud]

MySQL 4.1.22

 

Hi,

 

I have two tables - CLIENTS & REMIND

 

Cutting out all the other fields,  below are the fields I want to use (the R 1 R2 etc are 'reminder' dates)

 

(1) CLIENTS

CLIENT_ID

CLIENT_NAME

 

(2)REMIND

CLIENT_ID

R1

R2

R3

R4

 

I would like to construct a query that tells if any of the dates in the R1 R2 R3 R4 fields is within 14 days of today.

 

 

I'm thinking along these lines....

SELECT 
CLIENTS.CLIENT_NAME, REMIND.R1, REMIND.R2, REMIND.R3, REMIND.R4
FROM
CLIENTS, REMIND
WHERE
CLIENTS.CLIENT_ID = REMIND.CLIENT_ID AND REMIND.R1 BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL '$numberOfDays' DAY)

 

...but I don't know how to incorporate all four 'R' fields.

 

I'd really appreciate your assistance

 

[fenway]

Just chain them together with OR... what have you tried that didn't work?

[tHud]

 

EDIT - I think I must have Dyslexia - I read your answer 4 times and still only read your sig...

 

 

Well I would appreciate your help.

 

I included the MySQL type and put the sql in a code block - but I'm not sure what raw means.

 

I don't have any errors as I am unable to complete the sql.

 

I thought that I had given the description but if I've not been clear can you please tell me which part to elaborate on.

 

The reason I didn't include the SHOW CREATE TABLE was only because I was trying to simplify the question.

 

However, here they are.

CREATE TABLE `CLIENTS` (
  `CLIENT_ID` int(4) NOT NULL auto_increment,
  `CLIENT_NAME` varchar(100) NOT NULL default '',
  `CLIENT_ADDRESS_1` varchar(100) NOT NULL default '',
  `CLIENT_ADDRESS_2` varchar(100) NOT NULL default '',
  `CLIENT_TOWN` varchar(50) NOT NULL default '',
  `CLIENT_COUNTY` varchar(20) NOT NULL default '',
  `CLIENT_POSTCODE` varchar(10) NOT NULL default '',
  `CLIENT_TELEPHONE` varchar(20) NOT NULL default '0',
  `CLIENT_FAX` varchar(20) NOT NULL default '0',
  `CLIENT_CONTACT_1` varchar(20) NOT NULL default '',
  `CLIENT_CONTACT_1_TELEPHONE` varchar(20) NOT NULL default '0',
  `CLIENT_CONTACT_2` varchar(20) NOT NULL default '',
  `CLIENT_CONTACT_2_TELEPHONE` varchar(20) NOT NULL default '0',
  PRIMARY KEY  (`CLIENT_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;
        

 

CREATE TABLE `REMIND` (
  `REMIND_ID` int(4) NOT NULL auto_increment,
  `CLIENT_ID` int(4) NOT NULL default '0',
  `R1` date default NULL,
  `R1_INFO` varchar(255) NOT NULL default '',
  `R2` date default NULL,
  `R2_INFO` varchar(255) NOT NULL default '',
  `R3` date default NULL,
  `R3_INFO` varchar(255) NOT NULL default '',
  `R4` date default NULL,
  `R4_INFO` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`REMIND_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
        

 

I am sure there are errors in the types of fields I have created because of my inexperience.

 

 

If there is anything else I have missed please let me know and I will do my best to explain.

 

Thank you

[tHud]

Well, I tried this (below) but I just got columns of CLIENTS with rows of identical dates.

 

SELECT 
CLIENTS.CLIENT_NAME, REMIND.R1, REMIND.R2, REMIND.R3, REMIND.R4
FROM
`CLIENTS`, `REMIND`
WHERE
CLIENTS.CLIENT_ID = REMIND.CLIENT_ID
AND
REMIND.R1 || REMIND.R2 || REMIND.R3 || REMIND.R4 BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 14 DAY)

[fenway]

No, no no no no.

 

You need the full comparison for each field.

[tHud]

ok - I tried this... (Is it what you mean?)

 

SELECT 
CLIENTS.CLIENT_NAME, REMIND.R1, REMIND.R2, REMIND.R3, REMIND.R4
FROM
`CLIENTS`, `REMIND`
WHERE
CLIENTS.CLIENT_ID = REMIND.CLIENT_ID
AND
REMIND.R1  BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY)
|| REMIND.R2  BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY)
|| REMIND.R3  BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY)
|| REMIND.R4 BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY)

 

But I get rows of clients with date data alongside where I know certain clients don't have any date data entered.

 

a 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
b 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
c 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
d 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
e 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
f 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
g 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
h 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
i 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
j 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
k 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
l 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
m 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30
n 	2010-07-28 	2010-07-29 	2010-08-27 	2010-09-30

[tHud]

Incidentally, all those date records belong to CLIENT A

 

 

EDIT - please don't respond - I've noticed an anomaly that I need to look into before wasting anyone's time.

 

 

Thank you.

[jdavidbakr]

Separate your "or" section in parentheses:

 

...
WHERE
CLIENTS.CLIENT_ID = REMIND.CLIENT_ID
AND
(REMIND.R1  BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY)
|| REMIND.R2  BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY)
|| REMIND.R3  BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY)
|| REMIND.R4 BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 21 DAY))

[tHud]

oh yeah!

That was it.

 

Thanks Jon - greatly appreciated!