List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 25 2005 8:36pm
Subject:RE: Query question
View as plain text  
"Jeff McKeon" <jmckeon@stripped> 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:peter.brawley@stripped] 
> > Sent: Monday, April 25, 2005 4:01 PM
> > To: Jeff McKeon
> > Cc: mysql@stripped
> > 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
Thread
Query questionJeff McKeon25 Apr
  • Re: Query questionSGreen25 Apr
  • RE: Query questionmathias fatene25 Apr
    • RE: Query questionSGreen25 Apr
      • RE: Query questionmathias fatene25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
      • Re: Query questionPeter Brawley25 Apr
      • RE: Query questionSGreen25 Apr
        • RE: Query questionmathias fatene26 Apr
          • Re: Query questionMartijn Tonies26 Apr
            • RE: Query questionmathias fatene26 Apr
              • Re: Query questionChris Ramsay26 Apr
                • Re: Query questionmfatene26 Apr
  • Re: Query questionPeter Brawley25 Apr
  • RE: Query questionSGreen25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr