[snip]
SELECT customer.customer_id, customer.customer_name,
business_unit.business_unit, customer.created_date, CONCAT_WS(", ",
username.last_name, username.first_name), history.modify_date, CONCAT_WS(",
", modified.last_name, modified.first_name), history.description
FROM `customer`
LEFT OUTER JOIN `business_unit` ON business_unit.bu_id = customer.bu_id
LEFT OUTER JOIN `username` ON username.user_id = customer.creator_id
LEFT OUTER JOIN `history` ON ( history.item_id = customer.customer_id AND
history.module_id = 1003 )
LEFT OUTER JOIN `username` AS modified ON modified.user_id =
history.modifier_id
WHERE customer.customer_id LIKE "%man%"
OR customer.customer_name LIKE "%man%"
OR customer.created_date LIKE "%man%"
OR business_unit.business_unit LIKE "%man%"
OR username.first_name LIKE "%man%"
OR username.last_name LIKE "%man%"
OR history.modify_date LIKE "%man%"
OR history.description LIKE "%man%"
OR modified.first_name LIKE "%man%"
OR modified.last_name LIKE "%man%"
ORDER BY customer.customer_name
[/snip]
Try this;
SELECT customer.customer_id, customer.customer_name,
business_unit.business_unit, customer.created_date, CONCAT_WS(", ",
username.last_name, username.first_name), MAX(history.modify_date),
CONCAT_WS(",
", modified.last_name, modified.first_name), history.description
FROM `customer`
LEFT OUTER JOIN `business_unit` ON business_unit.bu_id = customer.bu_id
LEFT OUTER JOIN `username` ON username.user_id = customer.creator_id
LEFT OUTER JOIN `history` ON ( history.item_id = customer.customer_id AND
history.module_id = 1003 )
LEFT OUTER JOIN `username` AS modified ON modified.user_id =
history.modifier_id
WHERE customer.customer_id LIKE "%man%"
OR customer.customer_name LIKE "%man%"
OR customer.created_date LIKE "%man%"
OR business_unit.business_unit LIKE "%man%"
OR username.first_name LIKE "%man%"
OR username.last_name LIKE "%man%"
OR history.modify_date LIKE "%man%"
OR history.description LIKE "%man%"
OR modified.first_name LIKE "%man%"
OR modified.last_name LIKE "%man%"
GROUP BY customer.customer_name
ORDER BY customer.customer_name
Note the MAX(history.modify_date) in the SELECT and the GROUP BY
customer_name
HTH! Peace ...
Jay
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
