"(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' )"
i stavim tri broja (kako bi i trebalo da ispadne) sve radi OK. Ovaj se upit ponavlja par puta i uvijek dolazi nakon: race_result.race_id IN
Mislim da je stvar u optimizaciji i smanjivanju broja upita (a meni to baš i ne ide) pa bi Vas molio da mi pomognete jer mi je ovo strašno bitno i hitno. Evo cjelovitog koda:
Code:
SELECT DISTINCT
ident,
prvi_i_drugi_bodovi,
prvi_i_drugi_cunjevi,
prva_i_druga_vrata,
race_competitor.cla,
race_competitor.vehicle,
race_competitor.club,
race_competitor.first_name,
race_competitor.last_name
FROM
(SELECT ident, total_time, master.race_id,
ifnull((SELECT MIN((total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000)))
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' )),
9999999999) as prvi_bodovi,
ifnull((SELECT MIN((total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000)))
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))>prvi_bodovi),
9999999999) as drugi_bodovi,
(SELECT pylons
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=prvi_bodovi
) as prvi_cunjevi,
(SELECT pylons
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=drugi_bodovi
) as drugi_cunjevi,
(SELECT gates
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=prvi_bodovi
) as prva_vrata,
(SELECT gates
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=drugi_bodovi
) as druga_vrata,
(SELECT prva_vrata+druga_vrata) as prva_i_druga_vrata,
(SELECT prvi_bodovi+drugi_bodovi) as prvi_i_drugi_bodovi,
(SELECT prvi_cunjevi+drugi_cunjevi) as prvi_i_drugi_cunjevi
FROM race_result as master
WHERE master.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND NOT total_time=0
) as big_master
LEFT OUTER JOIN `race_competitor`
ON
race_competitor.st_number=big_master.ident
AND
race_competitor.race_id=big_master.race_id
ORDER BY big_master.prvi_i_drugi_bodovi
SELECT DISTINCT
ident,
prvi_i_drugi_bodovi,
prvi_i_drugi_cunjevi,
prva_i_druga_vrata,
race_competitor.cla,
race_competitor.vehicle,
race_competitor.club,
race_competitor.first_name,
race_competitor.last_name
FROM
(SELECT ident, total_time, master.race_id,
ifnull((SELECT MIN((total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000)))
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' )),
9999999999) as prvi_bodovi,
ifnull((SELECT MIN((total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000)))
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))>prvi_bodovi),
9999999999) as drugi_bodovi,
(SELECT pylons
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=prvi_bodovi
) as prvi_cunjevi,
(SELECT pylons
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=drugi_bodovi
) as drugi_cunjevi,
(SELECT gates
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=prvi_bodovi
) as prva_vrata,
(SELECT gates
FROM race_result
LEFT OUTER JOIN `autoslalom_penalties`
ON
autoslalom_penalties.st_number=race_result.ident
AND
autoslalom_penalties.race_id=race_result.race_id
WHERE ident=master.ident AND NOT total_time=0 AND race_result.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND (total_time+(ifnull(pylons,0)*2000)+(ifnull(gates,0)*20000))=drugi_bodovi
) as druga_vrata,
(SELECT prva_vrata+druga_vrata) as prva_i_druga_vrata,
(SELECT prvi_bodovi+drugi_bodovi) as prvi_i_drugi_bodovi,
(SELECT prvi_cunjevi+drugi_cunjevi) as prvi_i_drugi_cunjevi
FROM race_result as master
WHERE master.race_id IN
(SELECT race.race_id from event
left outer join race_grp on event.event_id = race_grp.event_id
left outer join race on race.race_group_id = race_grp.race_group_id
where event.event_id = " . $event . " AND race.race_type_id = '1' ) AND NOT total_time=0
) as big_master
LEFT OUTER JOIN `race_competitor`
ON
race_competitor.st_number=big_master.ident
AND
race_competitor.race_id=big_master.race_id
ORDER BY big_master.prvi_i_drugi_bodovi
HVALA UNAPRIJED
[Ovu poruku je menjao misk0 dana 20.07.2008. u 09:47 GMT+1]