Pozdrav,
Imam sledeci problem,
SELECT DISTINCT upit sa vise LEFT JOIN -a duplira neke rezultate
na verziji mysql -a 3.23.58, dok na verziji 3.23.56 radi ocekivano (kako bi ja hteo
)
Evo upita:
SELECT DISTINCT (cs_update_queries.reference_number)
FROM cs_update_queries, cs_update_draft_stage, cs_listing_numbers, cs_personal_info, cs_personal_info a
LEFT JOIN cs_listing_info ON cs_listing_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_rental_info ON cs_rental_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_appraisal_info ON cs_appraisal_info.property_id = cs_update_queries.property_id
LEFT JOIN cs_personal_info l ON l.person_id = cs_listing_info.assigned_to
LEFT JOIN cs_personal_info r ON r.person_id = cs_rental_info.assigned_to
LEFT JOIN cs_personal_info ap ON ap.person_id = cs_appraisal_info.assigned_to
WHERE cs_update_queries.completed = '0'
AND cs_update_queries.reference_number = cs_update_draft_stage.reference_number
AND cs_update_queries.property_id = cs_listing_numbers.listing_id
AND cs_update_queries.submitter_id = cs_personal_info.person_id
AND a.person_id
IN ( 0, 11837, 11668, 11658, 11921, 11652, 11675, 16580, 11665, 12485, 11679, 11672, 11666, 11688 ) AND ((l.person_id = a.person_id OR cs_listing_info.assigned_to = 0)
OR (r.person_id = a.person_id OR cs_rental_info.assigned_to = 0)
OR (ap.person_id = a.person_id OR cs_appraisal_info.assigned_to = 0))
AND cs_update_draft_stage.status = 'waiting'
AND (field_name
NOT IN ('assigned_to', 'present_status', 'listing_price', 'date_sold', 'selling_price', 'buyer_id', 'price_text_id', 'rental_status')
AND table_name <> 'cs_agency_info'
AND (field_name <> 'status' OR table_name <> 'cs_listing_info'))
AND office_id IN ( 0, 2, 1, 8, 6 )
ORDER BY cs_update_queries.date
LIMIT 10 , 10
ovo su rezultati na 3.23.56: nema duplikata
11665_20051220085047
11688_20051230162313
11665_20060106140929
11837_20060125114140
16580_20060125144715
11672_20060125162818
11672_20060126143555
11672_20060127121658
11672_20060127125540
11672_20060127130022
ovo su rezultati na 3.23.58:
11672_20060127131244
11672_20060130103227
11672_20060130104337
11672_20060130104643
11672_20060130104643
11672_20060130104908
11672_20060130111706
11672_20060130111706
11672_20060130111706
11675_20060201104657
Jel' ima neko ideju zasto se ovo desava?
Inace, na prvoj strani LIMIT 0, 10 nema ponavljanja.