22-01-2009, 19:12
|
מנהל
|
|
חבר מתאריך: 26.07.08
הודעות: 6,473
|
|
אני חושב שזה קשור לזה:
ציטוט:
במקור נכתב על ידי http://dev.mysql.com/doc/refman/5.0/en/join.html
INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).
However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.
|
http://dev.mysql.com/doc/refman/5.0/en/join.html
פשוט תשתמש ב- CROSS JOIN.
בעמוד הבא: http://www.wellho.net/mouth/513_MyS...correctly-.html
מוסבר מדוע כדאי להשתמש ב-JOIN (שבשביל MYSQL זה בדיוק CROSS JOIN) לעומת פסיקים ופיסקת WHERE.
לתאימות עם ה- Standard SQL, אמרתי שתשתמש ב-CROSS JOIN, כפי שכתוב בעמוד ה- MYSQL JOIN שקישרתי אליו למעלה.
כלומר הקוד צריך להיות:
קוד:
SELECT
`cadets`.`fname`,
`cadets`.`lname`,
`work_hours`.`startHour`,
`work_hours`.`endHour`,
`work_hours`.`purposeTime`,
`work_hours`.`personId`,
`classes_session_attend`.`attend`
FROM `cadets` CROSS JOIN `classes` CROSS JOIN `cadets_classes` CROSS JOIN `work_hours`
LEFT JOIN `classes_session_attend` ON
`classes_session_attend`.`classId` = `classes`.`id` AND
`classes_session_attend`.`cadetId` = `cadets`.`id` AND
`classes_session_attend`.`hourId` = `work_hours`.`id`
WHERE
`classes`.`id` = 1 AND
`cadets_classes`.`classId` = `classes`.`id` AND
`cadets_classes`.`cadetId` = `cadets`.`id` AND
`classes`.`id` = `work_hours`.`statusId` AND
`work_hours`.`isEmp` = 0 AND
`work_hours`.`job` = TRUE
|