All,
I apologize up front for being off topic, but I don't want to have to
subscribe to other lists unless necessary. I know that someone on this list
should be able to help me out.
I have the SQL string below that I am running. The problem I'm having is
that the history table being called in the join has multiple entries
referencing back to the single entry in the customer table. What I want to
be able to do is return only the latest/newest entry by date in the history
table and not older entries. I assume that I need to add some conditional
to the outer join. The problem I have is that I don't know what the
conditional would be to allow returning the latest row by date.
Thanks in advance, and again I apologize for the OT question,
Dave Merritt
[EMAIL PROTECTED]
currently running this string:
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
which returns the following results:
'1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','2002-11-12 10:53:23','Burnstingle,
Robert','Additional changes made','1003',
'1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','2002-11-09 13:15:34','Merritt, Dave','A test of
history','1003',
'1050','Ameritool Manufacturing','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','NULL','','NULL','NULL',
'1053','Ammann-Yanmar','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','NULL','','NULL','NULL',
'1065','Art's Way Manufacturing','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', .....
What I'm trying to achieve is the above results both with only one row
returned for the 1040 item like so, and the row returned should be the row
with the latest/newest modified date:
1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','2002-11-12 10:53:23','Burnstingle,
Robert','Additional changes made','1003',
'1050','Ameritool Manufacturing','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','NULL','','NULL','NULL',
'1053','Ammann-Yanmar','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','NULL','','NULL','NULL',
'1065','Art's Way Manufacturing','Gas Springs, Industrial','2002-03-01
00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', .....
**************************************************************************************************
Any views, opinions or authorizations contained in this email are solely those of the
author and do not necessarily represent those of ArvinMeritor, Inc. If you are not
familiar with the corporate authority of the author, please obtain confirmation in
writing
of the content of this email prior to taking any action on the basis of the
information. If
you are not the intended recipient, you are hereby notified that any disclosure,
copying
or distribution of the information enclosed is strictly prohibited.
**************************************************************************************************
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
