Hi, i've got a really strange problem with a request using multiple left join and order by :
Here is the first sql query : i display two columns of my table CV and one column of table ANNOTATION_CV SELECT ID_CV, TITRECV,ANNOTATION_CV.ANNOTATION_CV FROM CV LEFT JOIN CV_POSE ON CV_POSE.FK_CV=CV.ID_CV AND CV_POSE.FK_BV_RECRUTEUR=20613 LEFT join LISTE_VALEURS AS EXPERIENCE ON EXPERIENCE.ID_LIVA=CV.FK_EXPERIENCE LEFT join ANNOTATION_CV ON ID_CV=ANNOTATION_CV.FK_CV AND ANNOTATION_CV.FK_BV_RECRUTEUR=20613 WHERE FK_ID_ASP=1 AND (EN_RECHERCHE='Actif' OR EN_RECHERCHE='Passif') ORDER BY DATE_DE_DERNIERE_CONNEXION desc,CHERCHE_CDI desc,CHERCHE_CDD desc, CHERCHE_FREELANCE desc,CHERCHE_STAGE desc,CHERCHE_ALTERNANCE desc,CHERCHE_JOB desc, CHERCHE_AUTRE desc,TITRECV Here is the result +--------+--------------------------------------+---------------+ | ID_CV | TITRECV | ANNOTATION_CV | +--------+--------------------------------------+---------------+ | 138487 | shgfhgfdhfhg | NULL | | 138488 | shgfhgfdhfhg | NULL | | 138489 | shgfhgfdhfhg | NULL | | 138490 | shgfhgfdhfhg | NULL | | 138491 | shgfhgfdhfhg | NULL | | 138492 | shgfhgfdhfhg | NULL | | 138493 | shgfhgfdhfhg | NULL | | 138500 | shgfhgfdhfhg | NULL | | 138501 | shgfhgfdhfhg | NULL | | 138502 | shgfhgfdhfhg | NULL | | 138503 | shgfhgfdhfhg | NULL | | 138504 | shgfhgfdhfhg | NULL | | 138505 | shgfhgfdhfhg | NULL | | 138506 | shgfhgfdhfhg | NULL | | 138494 | shgfhgfdhfhg | NULL | | 138495 | shgfhgfdhfhg | NULL | | 138496 | shgfhgfdhfhg | NULL | | 138497 | shgfhgfdhfhg | NULL | | 138498 | shgfhgfdhfhg | NULL | | 138499 | shgfhgfdhfhg | NULL | | 138507 | TFGYHDFHGDFJ | NULL | | 138508 | TFGYHDFHGDFJ | NULL | | 138509 | TFGYHDFHGDFJ | NULL | +--------+--------------------------------------+---------------+ And the explain command : +---------------+--------+-----------------------------+-----------+-------- -+------------------+------+---------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------------+--------+-----------------------------+-----------+-------- -+------------------+------+---------------------------------------------+ | CV | ref | INDX_EN_RECHERCHE,FK_ID_ASP | FK_ID_ASP | 3 | const | 5535 | where used; Using temporary; Using filesort | | CV_POSE | ref | CV_FK_BV_POSE,FK_CV | FK_CV | 5 | CV.ID_CV | 3 | | | EXPERIENCE | eq_ref | PRIMARY | PRIMARY | 4 | CV.FK_EXPERIENCE | 1 | Using index | | ANNOTATION_CV | ALL | FK_BV_RECRUTEUR,FK_CV | NULL | NULL | NULL | 8 | | +---------------+--------+-----------------------------+-----------+-------- -+------------------+------+---------------------------------------------+ Here is the second sql query (the same one, but with only conlums of table CV): i display two columns of my table CV SELECT ID_CV, TITRECV FROM CV LEFT JOIN CV_POSE ON CV_POSE.FK_CV=CV.ID_CV AND CV_POSE.FK_BV_RECRUTEUR=20613 LEFT join LISTE_VALEURS AS EXPERIENCE ON EXPERIENCE.ID_LIVA=CV.FK_EXPERIENCE LEFT join ANNOTATION_CV ON ID_CV=ANNOTATION_CV.FK_CV AND ANNOTATION_CV.FK_BV_RECRUTEUR=20613 WHERE FK_ID_ASP=1 AND (EN_RECHERCHE='Actif' OR EN_RECHERCHE='Passif') ORDER BY DATE_DE_DERNIERE_CONNEXION desc,CHERCHE_CDI desc,CHERCHE_CDD desc, CHERCHE_FREELANCE desc,CHERCHE_STAGE desc,CHERCHE_ALTERNANCE desc,CHERCHE_JOB desc, CHERCHE_AUTRE desc,TITRECV here is the result. +--------+--------------------------------------+ | ID_CV | TITRECV | +--------+--------------------------------------+ | 138501 | shgfhgfdhfhg | | 138502 | shgfhgfdhfhg | | 138503 | shgfhgfdhfhg | | 138504 | shgfhgfdhfhg | | 138487 | shgfhgfdhfhg | | 138505 | shgfhgfdhfhg | | 138488 | shgfhgfdhfhg | | 138506 | shgfhgfdhfhg | | 138489 | shgfhgfdhfhg | | 138494 | shgfhgfdhfhg | | 138490 | shgfhgfdhfhg | | 138495 | shgfhgfdhfhg | | 138491 | shgfhgfdhfhg | | 138496 | shgfhgfdhfhg | | 138492 | shgfhgfdhfhg | | 138497 | shgfhgfdhfhg | | 138493 | shgfhgfdhfhg | | 138498 | shgfhgfdhfhg | | 138499 | shgfhgfdhfhg | | 138500 | shgfhgfdhfhg | | 138508 | TFGYHDFHGDFJ | | 138509 | TFGYHDFHGDFJ | | 138507 | TFGYHDFHGDFJ | +--------+--------------------------------------+ And the explain command : +---------------+--------+-----------------------------+-----------+-------- -+------------------+------+---------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------------+--------+-----------------------------+-----------+-------- -+------------------+------+---------------------------------------------+ | CV | ref | INDX_EN_RECHERCHE,FK_ID_ASP | FK_ID_ASP | 3 | const | 5535 | where used; Using temporary; Using filesort | | CV_POSE | ref | CV_FK_BV_POSE,FK_CV | FK_CV | 5 | CV.ID_CV | 3 | | | EXPERIENCE | eq_ref | PRIMARY | PRIMARY | 4 | CV.FK_EXPERIENCE | 1 | Using index | | ANNOTATION_CV | ALL | FK_BV_RECRUTEUR,FK_CV | NULL | NULL | NULL | 8 | | +---------------+--------+-----------------------------+-----------+-------- -+------------------+------+---------------------------------------------+ So, If you look at the two results, you can see that the result is the same but not the order !!!!!! What i don't undersdtand is that the two query are really the same, only the column displayed are different, but the order of the result is different !!!! Somebody have an idea !!!!!!!!! Thanks for your help ! Jerome Longet --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
