RE: Query question

[Jeff McKeon]

thanks, I'll give that a try.... tomorrow.  :o)
 
 
Jeffrey S. McKeon
Manager of Information Technology
Telaurus Communications LLC
[EMAIL PROTECTED]
+1 (973) 889-8990 ex 209

        -----Original Message-----
        From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
        Sent: Monday, April 25, 2005 4:36 PM
        To: Jeff McKeon
        Cc: mysql@lists.mysql.com
        Subject: RE: Query question
        
        


        "Jeff McKeon" <[EMAIL PROTECTED]> wrote on 04/25/2005
04:08:29 PM:
        
        > Thanks all but I don't have a mysql version high enough for
subqueries.
        > 
        > Thanks,
        > 
        > Jeff
        > 
        > > -----Original Message-----
        > > From: Peter Brawley [mailto:[EMAIL PROTECTED] 
        > > Sent: Monday, April 25, 2005 4:01 PM
        > > To: Jeff McKeon
        > > Cc: mysql@lists.mysql.com
        > > Subject: Re: Query question
        > > 
        > > 
        > > Jeff,
        > > 
        > > Something like ...
        > > 
        > > SELECT *
        > > FROM table2 AS a
        > > WHERE datestamp = (
        > >   SELECT MAX( b.datestamp )
        > >   FROM table2 AS b
        > >   WHERE a.parentID = b.parentID
        > > );
        > > 
        > > PB
        > > 
        > > -----
        > > 
        > > 
        > > Jeff McKeon wrote:
        > > 
        > > >I have a table that contains records that link back to a 
        > > main talbe in 
        > > >a many to one configuration linked by table1.id =
table2.parentid
        > > >
        > > >Table1 (one)
        > > >Table2 (many)
        > > >
        > > >I want to pull the latest records from table2 for each 
        > > record in table1 
        > > >where certain criteria applie.
        > > >
        > > >So, if record 100 in table1 links to 5 corresponding
records 
        > > in table2, 
        > > >I want to pull the latest record from table2 where
table2.parentid = 
        > > >100 and table2.user not like 'john'
        > > >
        > > >There is a datestamp field in table2.
        > > >
        > > >I just can't figure out how to do this.
        > > >
        > > >Thanks,
        > > >
        > > >Jeff
        > > >
        > > >
        > > >  
        > > >
        > > 
        > > 
        > > -- 
        > > No virus found in this outgoing message.
        > > Checked by AVG Anti-Virus.
        > > Version: 7.0.308 / Virus Database: 266.10.2 - Release Date:
4/21/2005
        > > 
        > > 
        
        OK, then you need to collect your child-table maximums in one
pass and build your actual query in the second (the non-subquery version
of the example I sent). Let's find all of the child records where user
not like 'john'. ( I will exclude all users whose name starts with
'john') 
        
        CREATE TEMPORARY TABLE lastRecords 
        SELECT parentID, max(datetime_field_name_here) as latest 
        FROM table2 
        WHERE user NOT LIKE 'john%' 
        GROUP BY parentID; 
        
        You had to exclude 'john' at this stage because you want the
latest child record that isn't 'john'. Make sense? Of course, you will
need to adjust this to meet whatever conditions you really want. 
        
        SELECT t1.*, t2.* 
        FROM table1 t1 
        LEFT JOIN lastRecords r 
                ON r.parentID = t1.id 
        LEFT JOIN table2 t2 
                ON t2.parentID = r.parentID 
                AND t2.datetime_field_name_here = r.latest; 
        
        That will give you all of the records from table1 and only the
most recent record from table2 (if it even exists). I used the LEFT JOIN
(not an INNER JOIN) so that you can see all of the records from table1.
If I had used INNER JOINs you would have only seen those records that
matched up with the conditions you placed on table2. 
        
        Shawn Green
        Database Administrator
        Unimin Corporation - Spruce Pine