CODEWITHSUNDEEP
mysqlsql
Kieran Headley
Kieran Headley

Reputation: 993

Limit on a MySql join

I need to limit a join in a mysql query, I have the code below that I am using;

select customer.`name`, customer.`retainer_value`, updates.`date` 
from updates
  left join customer on customer.id = updates.`clientid`
WHERE customer.`retainer_value` < 250 
  AND customer.switchedoff = 'N' 
  AND customer.companyid <> 3 
  AND customer.retainer_value > 20 
group by updates.clientid 
order by updates.date DESC

I am trying to select all of the customers from the customers table that meet the where criteria and left join the latest update in the updates table for that customer.

Is there an easy way to do this or do I need to do it via PHP?

Upvotes: 0

Views: 112

Answers (2)

GarethD
GarethD

Reputation: 69829

You have fallen into the trap of the MySQL GROUP BY extension. Just because you have put ORDER BY Date DESC it does not mean that the date returned will be the latest.

The MySQL documents state:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause

But it also goes on to say:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.

It is for this reason that I always advise to avoid the impicit grouping that MySQL offers as it can very easily lead to non determistic queries.

So all your ORDER BY clause is doing is once any date has been retrived for each client, it orders the final result set in order of the date picked. If you want the latest update date, the only way to do this deterministically is to use the MAX function:

select customer.`name`, customer.`retainer_value`, MAX(updates.`date`) as Date
from updates
  inner join customer on customer.id = updates.`clientid`
WHERE customer.`retainer_value` < 250 
  AND customer.switchedoff = 'N' 
  AND customer.companyid <> 3 
  AND customer.retainer_value > 20 
group by updates.clientid, customer.`name`, customer.`retainer_value`;

N.B I have changed your left join to an inner join as your where clause had effectively done this anyway. If you want customers with no updates then you would need to change your clause to select from customers, then left join to updates

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

First, you need to do the left join in the right order, if you want to keep all customers that meet the filter. This means that the customer table should be first in the left join.

Then, if you jut want the most recent date, then use max():

select c.`name`, c.`retainer_value`, max(u.`date`)
from customer c left join
     updates u
     on c.id = u.`clientid`
WHERE c.`retainer_value` < 250 AND c.switchedoff = 'N' AND c.companyid <> 3 AND
      c.retainer_value > 20
group by c.id
order by max(u.`date`) DESC

Upvotes: 1

Related Questions