1146 :
SELECT SQL_CALC_FOUND_ROWS a.date_evene, a.lieu_evene, a.dept_evene
,b.id_indi, b.sosa_dyn, b.nom, b.prenom1, b.prenom2, b.prenom3, b.sexe
,c.id_indi, c.sosa_dyn, c.nom, c.prenom1, c.prenom2, c.prenom3, c.sexe
,d.nom, d.prenom1, d.prenom2, d.prenom3
,a.type_evene, a.anne_evene
,a.note_evene, e.id_sour
,long_evene
,lati_evene
,xpix_evene
,ypix_evene
FROM got__evenement a
INNER JOIN got__individu b ON a.id_indi = b.id_indi
LEFT OUTER JOIN got__individu c ON b.id_pere = c.id_indi
LEFT OUTER JOIN got__individu d ON b.id_mere = d.id_indi
LEFT OUTER JOIN got__even_sour e ON a.id_indi = e.id_indi and a.type_evene = e.type_evene AND e.type_sourc = "SOUR"
WHERE a.type_evene NOT IN ("NOTE","FILE","OCCU","CHAN","RFN","RIN")
AND a.type_evene like "BIRT"
AND b.sexe LIKE "_"
AND b.sosa_dyn >= 0
AND CONCAT(IFNULL(LOWER(b.nom),""),IFNULL(LOWER(a.anne_evene),""),IFNULL(LOWER(a.lieu_evene),""),IFNULL(LOWER(a.dept_evene),""),IFNULL(LOWER(c.nom),""),IFNULL(LOWER(d.nom),""),IFNULL(LOWER(a.note_evene),"")) LIKE "%%"
UNION ALL
SELECT a.date_evene, max(a.lieu_evene), max(a.dept_evene)
,a.id_husb, max(b.sosa_dyn), max(b.nom), max(b.prenom1), max(b.prenom2), max(b.prenom3), max(b.sexe)
,a.id_wife, max(c.sosa_dyn), max(c.nom), max(c.prenom1), max(c.prenom2), max(c.prenom3), max(c.sexe)
,"", "", "", ""
,max(a.type_evene), max(a.anne_evene), max(a.note_evene)
,max(ifnull(e.id_sour,0))
,MAX(long_evene)
,MAX(lati_evene)
,MAX(xpix_evene)
,MAX(ypix_evene)
FROM got__evenement a
LEFT OUTER JOIN got__individu b ON (a.id_husb = b.id_indi)
LEFT OUTER JOIN got__individu c ON (a.id_wife = c.id_indi)
LEFT OUTER JOIN got__even_sour e ON (a.id_husb = e.id_husb AND a.id_wife = e.id_wife and a.type_evene = e.type_evene)
WHERE a.type_evene in ("MARR","MARZ","MARC","MARB","MARL","MARS","DIV","ENGA","ORDI","EVEN")
AND a.type_evene like "BIRT"
AND CONCAT(IFNULL(LOWER(b.nom),""),IFNULL(LOWER(c.nom),""),IFNULL(LOWER(a.anne_evene),""),IFNULL(LOWER(a.lieu_evene),""),IFNULL(LOWER(a.dept_evene),""),IFNULL(LOWER(a.note_evene),"")) LIKE "%%"
AND (b.sosa_dyn >= 0 OR c.sosa_dyn >= 0)
GROUP BY a.date_evene, a.id_husb, a.id_wife
ORDER BY 6, 6
LIMIT 0,50 1146 :
SELECT SQL_CALC_FOUND_ROWS DISTINCT a.lieu_evene, a.dept_evene,xpix_evene,ypix_evene,long_evene,lati_evene
FROM got__evenement a
INNER JOIN got__individu b ON a.id_indi = b.id_indi
LEFT OUTER JOIN got__individu c ON b.id_pere = c.id_indi
LEFT OUTER JOIN got__individu d ON b.id_mere = d.id_indi
WHERE a.type_evene NOT IN ("NOTE","FILE","OCCU","CHAN","RFN","RIN")
AND a.type_evene like "BIRT"
AND b.sexe LIKE "_"
AND b.sosa_dyn >= 0
AND CONCAT(IFNULL(LOWER(b.nom),""),IFNULL(LOWER(a.anne_evene),""),IFNULL(LOWER(a.lieu_evene),""),IFNULL(LOWER(a.dept_evene),""),IFNULL(LOWER(c.nom),""),IFNULL(LOWER(d.nom),""),IFNULL(LOWER(a.note_evene),"")) LIKE "%%"
AND xpix_evene != 0
UNION
SELECT DISTINCT a.lieu_evene, a.dept_evene, xpix_evene, ypix_evene,long_evene,lati_evene
FROM got__evenement a
LEFT OUTER JOIN got__individu b ON (a.id_husb = b.id_indi)
LEFT OUTER JOIN got__individu c ON (a.id_wife = c.id_indi)
WHERE a.type_evene in ("MARR","MARZ","MARC","MARB","MARL","MARS","DIV","ENGA","ORDI")
AND a.type_evene like "BIRT"
AND CONCAT(IFNULL(LOWER(b.nom),""),IFNULL(LOWER(c.nom),""),IFNULL(LOWER(a.anne_evene),""),IFNULL(LOWER(a.lieu_evene),""),IFNULL(LOWER(a.dept_evene),""),IFNULL(LOWER(a.note_evene),"")) LIKE "%%"
AND (b.sosa_dyn >= 0 OR c.sosa_dyn >= 0)
AND xpix_evene != 0
ORDER BY 2,1
|