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
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)