Query joining...HELP

Discussion in 'MySQL' started by KingCobra, Mar 26, 2010.

  1. #1
    Query joining:


    First Query:
    select teams,starttime,serie,count(distinct(bookie)) as bookie,round(max(home),2) as home,round(max(draw),2) as draw, round(max(away),2) as away from $this->table where home>0 and draw>0 and away>0 and serie != '' and starttime != '0000-00-00 00:00:00' group by teams having bookie > 2 order by starttime,serie limit 450

    Looping results with second query:

    while ($row = $this->fetch()) {


    /*Due to different timezones one of the starttimes can be wrong,so I will need only the start time with most values
      example:
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 18:30:00
    2010-06-11 18:30:00
    2010-06-11 18:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 18:30:00
    2010-06-11 19:30:00
    2010-06-11 18:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-12 13:35:00
    2010-06-12 13:35:00
    2010-06-11 20:30:00
    2010-06-11 19:30:00
    2010-06-11 18:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 18:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00
    2010-06-11 19:30:00 --> correct starttime is 2010-06-11 19:30:00
    */

    $query = "SELECT starttime AS basevalue
    FROM $this->table
    WHERE teams = '$row[teams]'
    GROUP BY basevalue
    ORDER BY count( starttime ) DESC
    LIMIT 1";
    $correcttime = mysql_fetch_assoc ( mysql_query ( $query ) );
    $basevalue = $avgtime ['basevalue'];
    $starttime = explode(" ", $basevalue);
    //continue

    }


    Is there a way to join these two queries to one query?
     
    KingCobra, Mar 26, 2010 IP