List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 25 2005 8:41pm
Subject:Re: Query question
View as plain text  
Mathias,

>Im sorry to disappoint you but this is an anti-performance solution.
>Use joins rathers than subqueries, and don't use joins if you can (all 
data in the mother table).

This 2-query solution uses neither a join nor a subquery. What do you mean?

PB

-----


mathias fatene wrote:

> Hi,
> Im sorry to disappoint you but this is an anti-performance solution.
> Use joins rathers than subqueries, and don't use joins if you can (all 
> data in the mother table).
>  
> Imagine that table2 has 30.000.000 records, and not good indexes. you 
> can wait for your answer a long time.
>  
> Best Regards
> --------------------
> Mathias FATENE
>  
> Hope that helps
> /*This not an official mysql support answer/
>  
>
>     -----Original Message-----
>     *From:* Peter Brawley [mailto:peter.brawley@stripped]
>     *Sent:* lundi 25 avril 2005 22:17
>     *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
>  
>

Attachment: [text/html]
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
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