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;
  • The 3rd and the 2nd query should produce identical results (mind the order). I can't believe you when you say they don't. Can you share with us the output of both? – ypercubeᵀᴹ Jun 10 '15 at 8:51
  • You guys are really punctilious and I praise you for that. In my case it does. I tried to put a simplified version here so I get my answer rather than comments on my query structure. I just put my actual query in there for the curious minds :) – Masoud Jun 10 '15 at 9:36
  • 1
    You could also use the first query, just replace FROM a,b with FROM a CROSS JOIN b – ypercubeᵀᴹ Jun 10 '15 at 10:40
  • Why isn't the userid used in the joins? – ypercubeᵀᴹ Jun 10 '15 at 12:56
  • Yeah it must be there. for some unknown reason it's missing and the And only remains. I just fixed it – Masoud Jun 10 '15 at 13:13
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;
  • Yeah it does look weird. That is why i didn't put the actual query here because every one will b distracted with the query itself. my c table need to be left join to the cross product of a and b. that's my only option and I achieved it using sub query which I would rather not to use. adding the sub query there slows down my query.Moreover, a not being visible is strange to me. – Masoud Jun 10 '15 at 7:06
  • @Masoud then check the update - it is only about precedence of , and join - so you need to override with parens. – jkavalik Jun 10 '15 at 7:52
  • that worked tnx – Masoud Jun 10 '15 at 7:57

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.