From: Peter Brawley Date: April 25 2005 9:07pm Subject: Re: Query question List-Archive: http://lists.mysql.com/mysql/183108 Message-Id: <426D5C0A.9010704@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-426D5C0A2434=======" --=======AVGMAIL-426D5C0A2434======= Content-Type: multipart/alternative; boundary=------------040505060306000105030005 --------------040505060306000105030005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jeff, 3.23. no control over this right now or i'd upgrade, believe me! Yep, I maintain websites with the same problem. Shawn Green just posted a solution that doesn't need inline user variable assignment. PB ----- Jeff McKeon wrote: > 3.23. > > no control over this right now or i'd upgrade, believe me! > > > jeff > > -----Original Message----- > *From:* Peter Brawley [mailto:peter.brawley@stripped] > *Sent:* Monday, April 25, 2005 4:43 PM > *To:* Jeff McKeon > *Cc:* mysql@stripped > *Subject:* Re: Query question > > That's real syntax for inline assignment of a column value to a > user variable. What MySQL version are you using? > > PB > > Jeff McKeon wrote: > >> Peter, >> >> I'm unfamiliar with the "@d := " section you describe. Is this >> psudo code or real syntax? >> >> thanks, >> >> Jeff >> >> >> >> -----Original Message----- >> *From:* Peter Brawley [mailto:peter.brawley@stripped] >> *Sent:* Monday, April 25, 2005 4:17 PM >> *To:* Jeff McKeon >> *Cc:* mysql@stripped >> *Subject:* Re: Query question >> >> Jeff, >> >> Then do it with 2 queries, >> >>SELECT @d := MAX( datestamp ) >>FROM table2 >>WHERE parentID = X; >> >> SELECT * >> FROM table2 >> WHERE parentID = X AND datestamp=@d; >> >> PB >> >> ----- >> >> Jeff McKeon wrote: >> >>>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 >>>> >>>> >>>> >>>> >>> >>> >>> >>> >>------------------------------------------------------------------------ >> >>No virus found in this incoming message. >>Checked by AVG Anti-Virus. >>Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 >> >> >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 > > --------------040505060306000105030005 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
Jeff,

3.23.
 
no control over this right now or i'd upgrade, believe me!

Yep, I maintain websites with the same problem. Shawn Green just posted a solution that doesn't need inline user variable assignment.

PB

-----


Jeff McKeon wrote:
Message
3.23.
 
no control over this right now or i'd upgrade, believe me!
 
 
jeff
-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped]
Sent: Monday, April 25, 2005 4:43 PM
To: Jeff McKeon
Cc: mysql@stripped
Subject: Re: Query question

That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using?

PB

Jeff McKeon wrote:
Peter,
 
I'm unfamiliar with the "@d := " section you describe.  Is this psudo code or real syntax?
 
thanks,
 
Jeff
 
 
-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped]
Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@stripped
Subject: Re: Query question

Jeff,

Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;
SELECT *
FROM table2
WHERE parentID = X AND datestamp=@d;

PB

-----

Jeff McKeon wrote: 
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


    


  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--------------040505060306000105030005-- --=======AVGMAIL-426D5C0A2434======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-426D5C0A2434=======--