SQL left join

Posted by: 邱小新 at 下午2:02:00 in
  • 如果有要比對 LEFT JOIN TABLE 的條件，請放在 LEFT JOIN ON 做比對，WHERE 只能放主 TABLE 的條件比對。比如
    錯誤 SELECT b.WorkingHour FROM Employee a LEFT JOIN LaborCost b ON a.id=b.EmployeeID WHERE a.id=? AND b.Month LIKE '$filDate3%'。
    正確 SELECT b.WorkingHour FROM Employee a LEFT JOIN LaborCost b ON a.id=b.EmployeeID AND b.Month LIKE '$filDate3%' WHERE a.id=?。
  • 另外 GROUP BY，ORDER BY 也不能放子表的欄位，只能主表的欄位。比如
    錯誤 SELECT b.StageID, SUM(TIME_TO_SEC(b.Time))/60/60 From Stage a LEFT JOIN WorkingTime b ON a.id=b.StageID AND b.ProjectID=? and b.EmployeeID=? GROUP BY b.StageID ORDER BY b.StageID。
    正確 SELECT a.id, SUM(TIME_TO_SEC(b.Time))/60/60 From Stage a LEFT JOIN WorkingTime b ON a.id=b.StageID AND b.ProjectID=? and b.EmployeeID=? GROUP BY a.id ORDER BY a.id。
  • How to LEFT JOIN Multiple Tables in SQL

0 意見

張貼留言

較新的文章 較舊的文章