Cross Join and Left Join within a query

Question

I have query which has a cross join and also a left join which produces an error

 Unknown column 'a.hour' in 'on clause'

simplified version of query is like this

SELECT b.*,a.hour,c.datetime
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

but when this query written as below it works.

select f.*,c.datetime
from
(select a.Hour,b.sec FROM a, b)f
LEFT JOIN c ON hour(c.datetime) = f.hour;

even this query works but result is not what I expect

select b.*,a.hour.c.datetime
from
a
LEFT JOIN c ON hour(c.datetime) = hrs.hour,b;

This looks very strange to me. Can anybody explain to me why is this happening? I have tried this on mysql 5.0 and 5.6. both having the same result.

Bellow is my actual query That i am working on. If it looks too large and awkward is because I am trying to achieve full outer join without repeating one of the main queries twice. (Left And Right Join). Hrs table contains 1 to 24 and text like '1:00 to 2:00 Pm'.

SELECT DnT.text,IFNULL(BrowsingTime.username,upAppUsage.username) as  username,dDate,
IFNULL(BrowsingTime.wDay,upAppUsage.wDay) as wDay,
productive,
unProductive,
Total,
Game,
Chat,
Video
FROM
(
    SELECT hrs.hour,hrs.text,dates.dDate
    FROM
    hrs,
    -- ALL THE user,DATE WITH REPORT
    (
        SELECT date(surfDateTime) as dDate,userid
        FROM browsingtime
        WHERE
        DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
        UNION
        SELECT date(snapshotDateTime)as dDate,userid
        FROM AppUsage_user_hour
        WHERE
        DATE(snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
    )dates
) AS DnT
LEFT JOIN
-- USER BROWSING TIME PER HOUR PER DATE
(
    SELECT rb.userId,CONCAT('(',user.id,') ',user.username) AS username, 
    date(surfDateTime) surfdate, HOUR(surfDateTime)as hr,DAYNAME(surfDateTime) AS wDay,
    SUM(IF(up.isProductive = 1,rb.duration,0)) as productive , 
    SUM(IF(up.isProductive = 0,rb.duration,0)) as unProductive,
    SUM(rb.duration) as Total
    FROM `user` 
    INNER JOIN usergroup ON usergroup.id = `user`.userGroupId 
    INNER JOIN browsingtime rb ON `user`.id =  rb.userId
    LEFT OUTER JOIN url_productive up ON up.urlshortid = rb.urlShortId 
    WHERE
    DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
    AND user.userGroupId IN (1,2,3,4,5,6,7,8,9,10) 
    GROUP BY surfdate, hr,rb.userId
)as BrowsingTime ON DnT.hour = BrowsingTime.hr AND BrowsingTime.surfdate = DnT.dDate AND DnT.userId = BrowsingTime.userId
LEFT JOIN
-- APPLICATION USAGE
(
    SELECT r.userId,CONCAT('(',u.id,') ',u.username) AS username,
    date(r.snapshotDateTime)as usageDate, HOUR(r.snapshotDateTime)as hr,DAYNAME(r.snapshotDateTime) AS wDay,
    SUM(IF(sl.categoryId = 2 , softwareActiveTime,0)) as Game ,
    SUM(IF(sl.categoryId = 9 , softwareActiveTime,0)) as chat ,
    SUM(IF(sl.categoryId = 8 , softwareActiveTime,0)) as video 
    FROM AppUsage_user_hour r 
    INNER JOIN USER u ON u.id = r.userId
    INNER JOIN usergroup ON usergroup.id = u.userGroupId 
    INNER JOIN clientpc cp ON cp.id = r.pcId  
    INNER JOIN softwares sl ON sl.id = r.softwareId 
    WHERE sl.categoryId IN (2,8,9)
    AND  DATE(r.snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10' 
    GROUP BY usageDate,r.userId
)as upAppUsage ON DnT.hour = upAppUsage.hr AND upAppUsage.usageDate = DnT.dDate AND DnT.userId = upAppUsage.userid
WHERE
IFNULL(BrowsingTime.userId,upAppUsage.userId) IS NOT NULL;

Answer 1

Your query

SELECT b.*
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

means (parentheses added)

SELECT b.*
FROM
a,
(b LEFT JOIN c ON hour(c.datetime) = a.hour);

and that means a is not visible in the join. You may need to shuffle it around a bit

SELECT b.*
FROM
(a LEFT JOIN c ON hour(c.datetime) = a.hour), b;

http://sqlfiddle.com/#!9/4114a/2

That said, such cross-join seems weird, so make sure you actually need it that way.

Modified after the comment http://sqlfiddle.com/#!9/4114a/3

SELECT b.*
FROM
(a,b)
LEFT JOIN c ON hour(c.datetime) = a.hour;