1

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;
0
2

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.