I think this was lost in the "Why aren't there more CF progs out there" post, so Ill try again.
I almost have this query doing what I need. I want to select ONLY the MOST recent MyDetail.Entry_Date and MyDetail.Entry_Comment. Whats happening now is I get a SINGLE most recent MyDetail.Entry_Date (good) but MULTIPLE MyDetail.Entry_Comment 's (bad). Any suggestions or maybe an easier way to do this? SELECT a.Customer_Name,b.Entry_comments,c.MaxEntry_Date FROM MyMaster a LEFT OUTER JOIN MyDetail b ON a.id = b.MyMaster_ID LEFT JOIN (SELECT MyMaster_ID,MAX(Entry_Date) AS MaxEntry_Date FROM MyDetail GROUP BY MyMaster_ID) AS c ON b.MyMaster_ID = c.MyMaster_ID AND b.Entry_Date = c.MaxEntry_Date WHERE blah ORDER BY blah Here is the original Query, which works fine, it pulls all the Detail records for each Master: SELECT a.Customer_Name,b.Entry_Date,b.Entry_comments FROM MyMaster a LEFT OUTER JOIN MyDetail b ON a.id = b.MyMaster_ID WHERE blah ORDER BY blah Thanks,Adrian ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
