左连接子查询返回Null

Question

I am applying Left Join with Sub query & where clause

It seems fine no syntax error but the columns I am selecting from that sub query always returns me Null. I have executed the same part in my SQL section, it gives me record. Kindly have a look on the query and let me know if any thing is possible or if question is not clear.

SELECT alt.userId, u.name, t.name AS teamName, alt.startDateTime, v.name AS     villageName, c.name AS clusterName, startLat, startLong, latlng.lat, latlng.long 
FROM activity_log_tim AS alt 
JOIN user AS u ON u.userId = alt.userId
JOIN team_members AS tm ON tm.memberId = u.userId
JOIN team AS t ON t.teamId = tm.teamId
JOIN village AS v ON v.villageId = alt.villageId
JOIN cluster_villages AS cv ON cv.villageId = v.villageId
JOIN cluster AS c ON c.clusterId = cv.clusterId
LEFT JOIN (SELECT lat, long,dateTime, scheduleId FROM activity_log_gps LIMIT 1) AS latlng ON latlng.scheduleId = alt.scheduleId
WHERE DATE(alt.startDateTime) = '2015-09-05' AND DATE(alt.endDateTime) = '0000-00-00' GROUP BY alt.userId ORDER BY latlng.dateTime DESC 

Answer 1

Well it's supposed to since you are performing a LEFT JOIN which produces NULL if no match occurs. So either you can perform a INNER JOIN or use a COALESCE function like below

   SELECT alt.userId, u.name, 
t.name AS teamName, 
alt.startDateTime, 
v.name AS     
villageName, 
c.name AS clusterName, 
startLat, 
startLong, 
COALESCE(latlng.lat, 23), //Notice the use of COALESCE. If null will return 23 as default 
COALESCE(latlng.long, 32)