[RESOLVED] prob with sql syntax using MAX

[steamPunk]

Hi

I need a bit of help again

I'm trying to write a query that will select all clients that have ordered at least once but haven't ordered in the past three months.

so what i've done is to select various cols from the client table and then used a left join to the orders table ('commandes') with a MAX on the order dates ('comm_jour') to get the most recent order and then use a condition where the order has to be older than today - 90 days

unfortunately, I'm getting an error :

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

here's what the query looks like

"SELECT 
	t1.client_id,
	t1.client_nom,
	t1.client_prenom,
	t1.client_email,
	t1.CT_RAPPEL_TEST,
	MAX(t2.comm_jour) AS last_order
	FROM `client` AS t1 
	LEFT JOIN commandes AS t2 
	ON t2.client_id=t1.client_id  

	WHERE t1.mailed='0' 
	AND comm_jour!='' 
	AND comm_jour!='0000-00-00' 
	AND last_order<'".$comm_date."' 
	AND t1.client_email!=''	 
	GROUP BY t1.client_id 
	ORDER BY client_nom ASC 
	LIMIT 0 , 100 "

I've tried a lot of things and I think it might be something to do with the AS in MAX but I can't find for sure what the problem is ....

can anyone spot what i'm doing wrong ?

thanks

[steamPunk]

ok - I've stopped getting an error but now I'm getting the wrong results and it's because of the way i've written my query

Wht i need is a list of clients whose most recent order is more than 90 days old

and what I'm getting a list of nearly all the clients and for each one it's returning their most recent order that's more than 90 days old ...

here's the query as it stands

	
$query = "SELECT 
	t1.client_id,
	t1.client_nom,
	t1.client_prenom,
	t1.client_email,
	t1.CT_RAPPEL_TEST,
	MAX(t2.comm_jour) AS comm_jour
	FROM `client` AS t1 
	LEFT JOIN commandes AS t2 
	ON t2.client_id=t1.client_id  

	WHERE t1.mailed='0' 
	AND comm_jour!='' 
	AND comm_jour!='0000-00-00' 
	AND (comm_jour<'".$comm_date."') 
	AND t1.client_email!=''	 
	GROUP BY t1.client_id 
	ORDER BY client_nom ASC";	

how would i need to change that so that it only returned clients whose most recent order is older than 90 days ?

thanks again

[Lars_Berg]

select t1.client_id,
       t1.client_nom,
       t1.client_prenom,
       t1.client_email,
       t1.CT_RAPPEL_TEST,
       MAX(t2.comm_jour) AS comm_jour
  from `client` AS t1
  join commandes AS t2 on t2.client_id = t1.client_id  

 where t1.mailed = 0
   and comm_jour <> ''
   and comm_jour <> '0000-00-00'
   and t1.client_email <> ''    

 group by t1.client_id
having MAX(t2.comm_jour) < current_date - interval '90' day
 order by client_nom ASC

[steamPunk]

Hi Lars

that query seems to work perfectly !

I've never used HAVING before so I think I have some reading to do ....

many thanks 😃😃😃