|
It's seem to be a problem on Joined sub-query (or view) :
*When i run this I have not problem :
*
SELECT `evt`.`id_evenement` AS `id_evenement`,
`grpEvt`.`libelle_public` AS `nom_evenement`,
`p`.`id_personne` AS `id_personne`,
`bv_volontid`.`valeur` AS `volont_id`,
`i`.`id_inscription` AS `id_inscription`,
`i`.`reference` AS `reference_i`,
IF(`i`.`desiste` = 1, 1, 0) AS `desiste`,
concat(ifnull(`civ`.`nom_abrege`, ''), ifnull(`civ`.`exposant`, '')) AS `civilite`,
concat(ifnull(ucase(`p`.`nom`), ''), IF(octet_length(`p`.`particule`) > 0, concat(' (', `p`.`particule`, ')'), '')) AS `nom`,
ifnull(`p`.`particule`, '') AS `particule`,
`p`.`prenom` AS `prenom`,
date_format(`p`.`date_naissance`, '%d/%m/%Y') AS `date_naissance`,
YEAR(`evt`.`date_debut` - INTERVAL to_days(`p`.`date_naissance`) DAY) AS `age`,
ifnull(`bv_p_branche`.`valeur`, '') AS `profession_branche`,
ifnull(`bv_p_metier`.`valeur`, '') AS `profession_metier`,
ifnull(`bv_p_specialite`.`valeur`, '') AS `profession_specialite`,
ucase(`c`.`adresse1`) AS `adresse1`,
ucase(`c`.`adresse2`) AS `adresse2`,
ucase(`c`.`adresse3`) AS `adresse3`,
`c`.`code_postal` AS `code_postal`,
ucase(`c`.`ville_cedex`) AS `ville`,
ucase(`c`.`pays_nom`) AS `pays`,
`c`.`telephone` AS `telephone`,
`c`.`mobile` AS `mobile`,
`c`.`email` AS `email`,
IF(
(YEAR(`evt`.`date_debut` - INTERVAL to_days(`p`.`date_naissance`) DAY)) < 18, 1, 0) AS `mineur`,
IF(`i`.`personne_malade` = 1
OR `i`.`personne_handicapee` = 1, 1, 0) AS `malade`,
`i`.`type_referent` AS `type_referent`,
CASE
WHEN (`i`.`type_referent` = 'P'
OR `i`.`type_referent` = 'GP') THEN ifnull(`p_ref`.`nom_usage`, '')
WHEN (`i`.`type_referent` = 'REF'
AND `i`.`referent_valide` <> 0) THEN ifnull(`p_ref`.`nom_usage`, '')
ELSE ''
END AS `nom_referent`,
CASE
WHEN `i`.`type_referent` LIKE 'REF' THEN `i`.`referent_valide`
ELSE NULL
END AS `referent_valide`,
IF(`i`.`confirm_reception_autorisation_parentale` = 0, 0, 1) AS `confirm_reception_autorisation_parentale`,
IF(YEAR(`evt`.`date_debut` - INTERVAL to_days(`p`.`date_naissance`) DAY) < 18
OR `i`.`personne_malade` = 1
OR `i`.`personne_handicapee` = 1, IF(`p`.`photo_filename` IS NOT NULL, 1, 0), NULL) AS `photo`,
`comite_dossier`.`libelle_public` AS `comite_di`,
IF(ifnull(`vit`.`id_inscription`, 0) <> 0, 'OUI', 'NON') AS `transport`,
ifnull(`vit`.`libelle_aller`, '') AS `transport_aller`,
ifnull(`vit`.`libelle_retour`, '') AS `transport_retour`,
ifnull(`vit`.`id_groupe_aller`, 0) = ifnull(`vit`.`id_groupe_ref_retour`, -1) AS `aller_retour`,
`i`.`date_arrivee` AS `date_arrivee_i`,
`i`.`date_depart` AS `date_depart_i`,
IF(`i`.`has_hebergement` = 1, 1, 0) AS `hebergement`,
ifnull(`th`.`libelle_public`, '') AS `nom_hebergement`,
ifnull(`bv_nds`.`valeur`, '') AS `nds`,
IF(`i`.`id_g_service_participation_service` IS NOT NULL, 1, 0) AS `souhaite_servir`,
IF(`i`.`id_g_service_participation_activite` IS NOT NULL, 1, 0) AS `souhaite_participer`,
IF(`i`.`id_g_service_participation_service` IS NOT NULL, 'SERVICE', IF(`i`.`id_g_service_participation_activite` IS NOT NULL, 'ACTIVITE', 'AUCUN')) AS `engagement`,
group_concat(DISTINCT ifnull(`activite_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `activite_souhait`,
group_concat(DISTINCT ifnull(`activite_valide`.`libelle_public`, '') SEPARATOR '\n') AS `activite_validee`,
group_concat(DISTINCT ifnull(`pole_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `pole_souhait`,
group_concat(DISTINCT ifnull(`pole_valide`.`libelle_public`, '') SEPARATOR '\n') AS `pole_valide`,
group_concat(DISTINCT ifnull(`service_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `service_souhait`,
group_concat(DISTINCT ifnull(`service_valide`.`libelle_public`, '') SEPARATOR '\n') AS `service_valide`,
group_concat(DISTINCT ifnull(`equipe_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `equipe_souhait`,
ifnull(`equipeval`.`libelle_public`, '') AS `equipe_validee`,
group_concat(DISTINCT ifnull(IF(`f`.`personne` IS NOT NULL, IF(`f`.`label_fonction` = 3, `labelf`.`nom_masculin`, IF(`p`.`sexe` = 'masculin', `labelf`.`nom_masculin`, `labelf`.`nom_feminin`)), ''), '') SEPARATOR '\n') AS `fonction`,
`i`.`affectation_commentaire` AS `affectation_commentaire`,
`i`.`mineur_has_premiere_communion` AS `mineur_has_premiere_communion`,
`i`.`mineur_autoriser_piscine` AS `mineur_autoriser_piscine`,
`i`.`mineur_autoriser_quitter_seul_activite` AS `mineur_autoriser_quitter_seul_activite`,
`di`.`id_dossier_inscription` AS `id_dossier_inscription`,
`di`.`reference` AS `reference_di`,
`di`.`libelle` AS `nom_di`,
IF(`di`.`paiement_differe` = 1, 'OUI', 'NON') AS `paiement_differe`,
`vue`.`montant_total` AS `montant_total_di`,
`vue`.`paiements` AS `paiements_di`,
`vue`.`solde` AS `solde_di`,
IF(`di`.`createur_comite` = 1, 'OUI', 'NON') AS `createur_comite`,
IF(`di`.`createur_central` = 1, 'OUI', 'NON') AS `createur_central`,
date_format(`di`.`date_creation`, '%d/%m/%Y') AS `date_creation_di`,
IF(`p`.`date_deces` IS NOT NULL, 'OUI', 'NON') AS `Personne Décédée`
FROM (((((((((((((((((((((((((((((((`inscription` `i`
JOIN `dossier_inscription` `di` ON(`di`.`id_dossier_inscription` = `i`.`id_dossier_inscription`))
JOIN `evenement` `evt` ON(`evt`.`id_evenement` = `di`.`id_evenement`))
JOIN `groupe` `grpEvt` ON(`grpEvt`.`id_evenement` = `evt`.`id_evenement`))
JOIN `personne` `p` ON(`p`.`id_personne` = `i`.`id_personne`))
JOIN `civilite` `civ` ON(`civ`.`id_civilite` = `p`.`civilite`))
LEFT JOIN `coordonnee` `c` ON(`c`.`personne` = `p`.`id_personne`
AND `c`.`defaut` = 1))
LEFT JOIN `groupe` `comite_dossier` ON(`comite_dossier`.`id_groupe` = `di`.`id_g_comite`))
LEFT JOIN `bonus_value` `bv_nds` ON(`bv_nds`.`id_bonus_setting` = 76
AND `bv_nds`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_volontid` ON(`bv_volontid`.`id_bonus_setting` = 92
AND `bv_volontid`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_p_branche` ON(`bv_p_branche`.`id_bonus_setting` = 99
AND `bv_p_branche`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_p_metier` ON(`bv_p_metier`.`id_bonus_setting` = 98
AND `bv_p_metier`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_p_specialite` ON(`bv_p_specialite`.`id_bonus_setting` = 97
AND `bv_p_specialite`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `service` `s` ON(`s`.`id_service` = `comite_dossier`.`service`))
LEFT JOIN `vue_ands_inscription_transport` `vit` ON(`vit`.`id_inscription` = `i`.`id_inscription`))
LEFT JOIN `hebergement_chambre` `hc` ON(`hc`.`id_hebergement_chambre` = `i`.`id_hebergement_chambre`))
LEFT JOIN `hebergement` `h` ON(`hc`.`id_hebergement` = `h`.`id_hebergement`))
LEFT JOIN `groupe` `th` ON(`th`.`id_hebergement` = `h`.`id_hebergement`))
LEFT JOIN `inscription_participation` `ip` ON(`ip`.`id_inscription` = `i`.`id_inscription`))
LEFT JOIN `groupe` `activite_souhait` ON(`activite_souhait`.`id_groupe` = `ip`.`id_g_activite`))
LEFT JOIN `groupe` `activite_valide` ON(`activite_valide`.`id_groupe` = `ip`.`id_g_activite`
AND `ip`.`id_fonction` IS NOT NULL))
LEFT JOIN `groupe` `pole_souhait` ON(`pole_souhait`.`id_groupe` = `ip`.`id_g_pole_souhait`))
LEFT JOIN `groupe` `service_souhait` ON(`service_souhait`.`id_groupe` = `ip`.`id_g_service_souhait`))
LEFT JOIN `groupe` `pole_valide` ON(`pole_valide`.`id_groupe` = `ip`.`id_g_pole`))
LEFT JOIN `groupe` `service_valide` ON(`service_valide`.`id_groupe` = `ip`.`id_g_service`))
LEFT JOIN `groupe` `equipe_souhait` ON(`equipe_souhait`.`id_groupe` = `ip`.`id_g_equipe_souhait`))
LEFT JOIN `groupe` `equipeval` ON(`equipeval`.`id_groupe` = `ip`.`id_g_equipe`))
LEFT JOIN `zone_geographique` `sousserv_geo` ON(`sousserv_geo`.`id_zone_geographique` = `service_souhait`.`zone_geographique`))
LEFT JOIN `fonction` `f` ON(`f`.`id_fonction` = `ip`.`id_fonction`))
LEFT JOIN `label_fonction` `labelf` ON(`f`.`label_fonction` = `labelf`.`id_label_fonction`))
LEFT JOIN `personne` `p_ref` ON(`p_ref`.`id_personne` = `i`.`id_personne_referente_calc`))
LEFT JOIN `vue_dossier_inscription_paiements` `vue` ON(`vue`.`id_dossier_inscription` = `di`.`id_dossier_inscription`))
WHERE (`sousserv_geo`.`type_zone` NOT IN (1,
2)
OR `sousserv_geo`.`type_zone` IS NULL)
AND `service_souhait`.`id_evenement` IS NULL
GROUP BY `i`.`id_inscription`
ORDER BY `p`.`nom`,
`p`.`prenom`
But when i ran this (just a join on table inscription) , the server CRASH :
SELECT ii.id_inscription FROM (SELECT `evt`.`id_evenement` AS `id_evenement`,
`grpEvt`.`libelle_public` AS `nom_evenement`,
`p`.`id_personne` AS `id_personne`,
`bv_volontid`.`valeur` AS `volont_id`,
`i`.`id_inscription` AS `id_inscription`,
`i`.`reference` AS `reference_i`,
IF(`i`.`desiste` = 1, 1, 0) AS `desiste`,
concat(ifnull(`civ`.`nom_abrege`, ''), ifnull(`civ`.`exposant`, '')) AS `civilite`,
concat(ifnull(ucase(`p`.`nom`), ''), IF(octet_length(`p`.`particule`) > 0, concat(' (', `p`.`particule`, ')'), '')) AS `nom`,
ifnull(`p`.`particule`, '') AS `particule`,
`p`.`prenom` AS `prenom`,
date_format(`p`.`date_naissance`, '%d/%m/%Y') AS `date_naissance`,
YEAR(`evt`.`date_debut` - INTERVAL to_days(`p`.`date_naissance`) DAY) AS `age`,
ifnull(`bv_p_branche`.`valeur`, '') AS `profession_branche`,
ifnull(`bv_p_metier`.`valeur`, '') AS `profession_metier`,
ifnull(`bv_p_specialite`.`valeur`, '') AS `profession_specialite`,
ucase(`c`.`adresse1`) AS `adresse1`,
ucase(`c`.`adresse2`) AS `adresse2`,
ucase(`c`.`adresse3`) AS `adresse3`,
`c`.`code_postal` AS `code_postal`,
ucase(`c`.`ville_cedex`) AS `ville`,
ucase(`c`.`pays_nom`) AS `pays`,
`c`.`telephone` AS `telephone`,
`c`.`mobile` AS `mobile`,
`c`.`email` AS `email`,
IF(
(YEAR(`evt`.`date_debut` - INTERVAL to_days(`p`.`date_naissance`) DAY)) < 18, 1, 0) AS `mineur`,
IF(`i`.`personne_malade` = 1
OR `i`.`personne_handicapee` = 1, 1, 0) AS `malade`,
`i`.`type_referent` AS `type_referent`,
CASE
WHEN (`i`.`type_referent` = 'P'
OR `i`.`type_referent` = 'GP') THEN ifnull(`p_ref`.`nom_usage`, '')
WHEN (`i`.`type_referent` = 'REF'
AND `i`.`referent_valide` <> 0) THEN ifnull(`p_ref`.`nom_usage`, '')
ELSE ''
END AS `nom_referent`,
CASE
WHEN `i`.`type_referent` LIKE 'REF' THEN `i`.`referent_valide`
ELSE NULL
END AS `referent_valide`,
IF(`i`.`confirm_reception_autorisation_parentale` = 0, 0, 1) AS `confirm_reception_autorisation_parentale`,
IF(YEAR(`evt`.`date_debut` - INTERVAL to_days(`p`.`date_naissance`) DAY) < 18
OR `i`.`personne_malade` = 1
OR `i`.`personne_handicapee` = 1, IF(`p`.`photo_filename` IS NOT NULL, 1, 0), NULL) AS `photo`,
`comite_dossier`.`libelle_public` AS `comite_di`,
IF(ifnull(`vit`.`id_inscription`, 0) <> 0, 'OUI', 'NON') AS `transport`,
ifnull(`vit`.`libelle_aller`, '') AS `transport_aller`,
ifnull(`vit`.`libelle_retour`, '') AS `transport_retour`,
ifnull(`vit`.`id_groupe_aller`, 0) = ifnull(`vit`.`id_groupe_ref_retour`, -1) AS `aller_retour`,
`i`.`date_arrivee` AS `date_arrivee_i`,
`i`.`date_depart` AS `date_depart_i`,
IF(`i`.`has_hebergement` = 1, 1, 0) AS `hebergement`,
ifnull(`th`.`libelle_public`, '') AS `nom_hebergement`,
ifnull(`bv_nds`.`valeur`, '') AS `nds`,
IF(`i`.`id_g_service_participation_service` IS NOT NULL, 1, 0) AS `souhaite_servir`,
IF(`i`.`id_g_service_participation_activite` IS NOT NULL, 1, 0) AS `souhaite_participer`,
IF(`i`.`id_g_service_participation_service` IS NOT NULL, 'SERVICE', IF(`i`.`id_g_service_participation_activite` IS NOT NULL, 'ACTIVITE', 'AUCUN')) AS `engagement`,
group_concat(DISTINCT ifnull(`activite_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `activite_souhait`,
group_concat(DISTINCT ifnull(`activite_valide`.`libelle_public`, '') SEPARATOR '\n') AS `activite_validee`,
group_concat(DISTINCT ifnull(`pole_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `pole_souhait`,
group_concat(DISTINCT ifnull(`pole_valide`.`libelle_public`, '') SEPARATOR '\n') AS `pole_valide`,
group_concat(DISTINCT ifnull(`service_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `service_souhait`,
group_concat(DISTINCT ifnull(`service_valide`.`libelle_public`, '') SEPARATOR '\n') AS `service_valide`,
group_concat(DISTINCT ifnull(`equipe_souhait`.`libelle_public`, '') SEPARATOR '\n') AS `equipe_souhait`,
ifnull(`equipeval`.`libelle_public`, '') AS `equipe_validee`,
group_concat(DISTINCT ifnull(IF(`f`.`personne` IS NOT NULL, IF(`f`.`label_fonction` = 3, `labelf`.`nom_masculin`, IF(`p`.`sexe` = 'masculin', `labelf`.`nom_masculin`, `labelf`.`nom_feminin`)), ''), '') SEPARATOR '\n') AS `fonction`,
`i`.`affectation_commentaire` AS `affectation_commentaire`,
`i`.`mineur_has_premiere_communion` AS `mineur_has_premiere_communion`,
`i`.`mineur_autoriser_piscine` AS `mineur_autoriser_piscine`,
`i`.`mineur_autoriser_quitter_seul_activite` AS `mineur_autoriser_quitter_seul_activite`,
`di`.`id_dossier_inscription` AS `id_dossier_inscription`,
`di`.`reference` AS `reference_di`,
`di`.`libelle` AS `nom_di`,
IF(`di`.`paiement_differe` = 1, 'OUI', 'NON') AS `paiement_differe`,
`vue`.`montant_total` AS `montant_total_di`,
`vue`.`paiements` AS `paiements_di`,
`vue`.`solde` AS `solde_di`,
IF(`di`.`createur_comite` = 1, 'OUI', 'NON') AS `createur_comite`,
IF(`di`.`createur_central` = 1, 'OUI', 'NON') AS `createur_central`,
date_format(`di`.`date_creation`, '%d/%m/%Y') AS `date_creation_di`,
IF(`p`.`date_deces` IS NOT NULL, 'OUI', 'NON') AS `Personne Décédée`
FROM (((((((((((((((((((((((((((((((`inscription` `i`
JOIN `dossier_inscription` `di` ON(`di`.`id_dossier_inscription` = `i`.`id_dossier_inscription`))
JOIN `evenement` `evt` ON(`evt`.`id_evenement` = `di`.`id_evenement`))
JOIN `groupe` `grpEvt` ON(`grpEvt`.`id_evenement` = `evt`.`id_evenement`))
JOIN `personne` `p` ON(`p`.`id_personne` = `i`.`id_personne`))
JOIN `civilite` `civ` ON(`civ`.`id_civilite` = `p`.`civilite`))
LEFT JOIN `coordonnee` `c` ON(`c`.`personne` = `p`.`id_personne`
AND `c`.`defaut` = 1))
LEFT JOIN `groupe` `comite_dossier` ON(`comite_dossier`.`id_groupe` = `di`.`id_g_comite`))
LEFT JOIN `bonus_value` `bv_nds` ON(`bv_nds`.`id_bonus_setting` = 76
AND `bv_nds`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_volontid` ON(`bv_volontid`.`id_bonus_setting` = 92
AND `bv_volontid`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_p_branche` ON(`bv_p_branche`.`id_bonus_setting` = 99
AND `bv_p_branche`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_p_metier` ON(`bv_p_metier`.`id_bonus_setting` = 98
AND `bv_p_metier`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `bonus_value` `bv_p_specialite` ON(`bv_p_specialite`.`id_bonus_setting` = 97
AND `bv_p_specialite`.`id_personne` = `p`.`id_personne`))
LEFT JOIN `service` `s` ON(`s`.`id_service` = `comite_dossier`.`service`))
LEFT JOIN `vue_ands_inscription_transport` `vit` ON(`vit`.`id_inscription` = `i`.`id_inscription`))
LEFT JOIN `hebergement_chambre` `hc` ON(`hc`.`id_hebergement_chambre` = `i`.`id_hebergement_chambre`))
LEFT JOIN `hebergement` `h` ON(`hc`.`id_hebergement` = `h`.`id_hebergement`))
LEFT JOIN `groupe` `th` ON(`th`.`id_hebergement` = `h`.`id_hebergement`))
LEFT JOIN `inscription_participation` `ip` ON(`ip`.`id_inscription` = `i`.`id_inscription`))
LEFT JOIN `groupe` `activite_souhait` ON(`activite_souhait`.`id_groupe` = `ip`.`id_g_activite`))
LEFT JOIN `groupe` `activite_valide` ON(`activite_valide`.`id_groupe` = `ip`.`id_g_activite`
AND `ip`.`id_fonction` IS NOT NULL))
LEFT JOIN `groupe` `pole_souhait` ON(`pole_souhait`.`id_groupe` = `ip`.`id_g_pole_souhait`))
LEFT JOIN `groupe` `service_souhait` ON(`service_souhait`.`id_groupe` = `ip`.`id_g_service_souhait`))
LEFT JOIN `groupe` `pole_valide` ON(`pole_valide`.`id_groupe` = `ip`.`id_g_pole`))
LEFT JOIN `groupe` `service_valide` ON(`service_valide`.`id_groupe` = `ip`.`id_g_service`))
LEFT JOIN `groupe` `equipe_souhait` ON(`equipe_souhait`.`id_groupe` = `ip`.`id_g_equipe_souhait`))
LEFT JOIN `groupe` `equipeval` ON(`equipeval`.`id_groupe` = `ip`.`id_g_equipe`))
LEFT JOIN `zone_geographique` `sousserv_geo` ON(`sousserv_geo`.`id_zone_geographique` = `service_souhait`.`zone_geographique`))
LEFT JOIN `fonction` `f` ON(`f`.`id_fonction` = `ip`.`id_fonction`))
LEFT JOIN `label_fonction` `labelf` ON(`f`.`label_fonction` = `labelf`.`id_label_fonction`))
LEFT JOIN `personne` `p_ref` ON(`p_ref`.`id_personne` = `i`.`id_personne_referente_calc`))
LEFT JOIN `vue_dossier_inscription_paiements` `vue` ON(`vue`.`id_dossier_inscription` = `di`.`id_dossier_inscription`))
WHERE (`sousserv_geo`.`type_zone` NOT IN (1,
2)
OR `sousserv_geo`.`type_zone` IS NULL)
AND `service_souhait`.`id_evenement` IS NULL
GROUP BY `i`.`id_inscription`
ORDER BY `p`.`nom`,
`p`.`prenom`) AS aa, inscription AS ii WHERE ii.id_inscription = aa.id_inscription
Hope that's help ....
|