Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    15
    Rep Power
    0

    Having "Unknown Column in On Clause" error...


    Hi guys, I'm working on fixing an error I'm having with a SQL statement that worked just fine in MySQL 4.1, and after I upgraded to 5.0 I'm now having the following troubles:

    Code:
    SELECT DISTINCT g.ObjectID, g.ItemID, u.AccessID AS uAccess, g.AccessID AS gAccess, g.GroupID
    FROM sysGroupPermissions AS g, tblUsers_Groups AS t
    LEFT JOIN sysUserPermissions AS u ON ( u.UserID = '2'
    AND u.ItemID = g.ItemID )
    WHERE t.UserID =2
    AND t.GroupID = g.GroupID
    ORDER BY g.ObjectID, g.AccessID DESC
    LIMIT 0 , 30
    
    MySQL said: Documentation
    #1054 - Unknown column 'g.ItemID' in 'on clause'
    Any help with this would be GREATLY appreciated!
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    See the link in my signature for information on correcting your JOIN. The problem is mixing LIST type joins with an OUTER join and the fact that mysql 5 is more particular in enforcing join integrity.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    15
    Rep Power
    0
    Guelph Ontario? What a small world! That's where I live hehe. Thanks for the info, I will look into it.

    Sweet, fixed!
  6. #4
  7. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    Yes im in the very same.

    There are a few folks on here from Toronto, Niagara and other areas, just not a lot of us.

    Welcome aboard!
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Guelphdad
    There are a few folks on here from Toronto, Niagara and other areas, just not a lot of us.
    everytime i spot someone from the local area, i add them to my buddy list

    just in case, you know, i need to PM them all some day about a neat local conference or something...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    beer at the skydome, er rogers center!
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    3
    Rep Power
    0

    Same Error, Need Help


    Hello guys, i do have a similar problem and the code is:

    define ('CU_SQL_GROUP_PERMISSIONS',
    'SELECT DISTINCT g.ObjectID, g.ItemID, u.AccessID AS uAccess, g.AccessID AS gAccess, g.GroupID
    FROM sysGroupPermissions g, tblUsers_Groups t
    LEFT JOIN sysUserPermissions u ON u.UserID = \'%1$s\' AND u.ItemID = g.ItemID
    WHERE \'%1$s\' = t.UserID AND t.GroupID = g.GroupID
    ORDER BY g.ObjectID, g.AccessID DESC');

    I am fairly new to PHP and mySQL and it would really help me if i can get the correct way the cod should be written, I'd really appreciate that... Thanks for any help =)
  14. #8
  15. Never give up
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Sep 2005
    Location
    College Station, TX, USA
    Posts
    2,625
    Rep Power
    809
    Yes you do have a similar problem. In fact, I would say it is almost identical. And the answer is still the same: get rid of the "list join" or "comma join" syntax.

    And why in the name of a deity you defining the query as a constant?

    So
    Code:
    select t1.id
       , t2.some_column
    from some_table as t1, another_table as t2
    where t1.some_column = t2.some_column
    should be
    Code:
    select t1.id
       , t2.some_column
    from some_table as t1
    inner
       join another_table as t2
          on t1.some_column = t2.some_column
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    3
    Rep Power
    0
    What I am working with is a project management software that was coded back in 2004 with older mySQL settings. it was working fine with the mysql 4.1 but now our server has be updated to the latest version of mysql and thus i get this error now and i am not able to create new projects. here is the full code.

    <?php
    // $Id: sql_mysql.php,v 1.3 2003-07-24 09:50:15-04 owner Exp owner $
    define('CU_SQL_SYSTEM_MODULES',
    'SELECT s.ID, s.Class, s.IsPublic, s.Name, s.Order
    FROM sysModules AS s
    WHERE MenuItem = 1 AND s.Order > 0
    ORDER BY s.Order ASC');

    define('CU_SQL_MENU_MODULES',
    'SELECT s.ID, s.Class, s.IsPublic, s.Name, s.Order
    FROM sysModules AS s
    WHERE MenuItem = 1 AND s.Order > 0
    ORDER BY s.Order ASC');

    define('CU_SQL_DENIED_PROJECT',
    'SELECT ProjectID FROM sysProjectPermissions WHERE UserID = \'%s\'');

    define('CU_SQL_USER_DETAILS',
    'SELECT u.Username, u.FirstName, u.LastName, u.EmailAddress, u.Phone1, u.Phone2, u.Phone3, u.Module, u.EmailNotify
    FROM tblUsers u WHERE u.ID = \'%s\'');

    define('CU_SQL_USER_PERMISSIONS',
    'SELECT u.ObjectID, u.ItemID, MAX( u.AccessID ) AS uAccess
    FROM sysUserPermissions u
    WHERE u.UserID = \'%1$s\'
    GROUP BY u.ObjectID, u.ItemID
    ORDER BY u.ObjectID ASC , u.AccessID DESC');

    //define('CU_SQL_USER_PERMISSIONS',
    // 'SELECT u.ObjectID, u.ItemID, MAX( u.AccessID ) AS uAccess, g.AccessID AS gAccess
    // FROM sysUserPermissions u
    // LEFT JOIN sysGroupPermissions g ON GroupID = (%2$s) AND u.ItemID = g.ItemID
    // WHERE u.UserID = \'%1$s\'
    // GROUP BY u.ObjectID, u.ItemID
    // ORDER BY u.ObjectID ASC , u.AccessID DESC');

    define('CU_SQL_GROUP_PERMISSIONS',
    'SELECT DISTINCT g.ObjectID, g.ItemID, u.AccessID AS uAccess, g.AccessID AS gAccess, g.GroupID
    FROM sysGroupPermissions g, tblUsers_Groups t
    LEFT JOIN sysUserPermissions u ON u.UserID = \'%1$s\' AND u.ItemID = g.ItemID
    WHERE \'%1$s\' = t.UserID AND t.GroupID = g.GroupID
    ORDER BY g.ObjectID, g.AccessID DESC');

    //define('CU_SQL_GROUP_PERMISSIONS',
    // 'SELECT g.ObjectID, g.ItemID, MAX( u.AccessID ) AS uAccess, g.AccessID AS gAccess
    // FROM sysGroupPermissions g
    // LEFT JOIN sysUserPermissions u ON UserID = \'%s\' AND u.ItemID = g.ItemID
    // WHERE g.GroupID = \'%s\' AND u.ItemID IS NULL
    // GROUP BY g.ObjectID, g.ItemID
    // ORDER BY g.ObjectID ASC , g.AccessID DESC');

    //define('CU_SQL_GROUP_PERMISSIONS_CLIENTS',
    // 'SELECT ObjectID, ItemID, MAX(AccessID) AS access
    // FROM sysPermissions WHERE GroupID = \'%s\' AND ObjectID = \'clients\'
    // GROUP BY ItemID ORDER BY AccessID DESC');

    //define('CU_SQL_GROUP_PERMISSIONS_PROJECTS',
    // 'SELECT s.ObjectID, s.ItemID, MAX(s.AccessID) AS access, t.ClientID
    // FROM sysPermissions s
    // LEFT JOIN tblProjects t ON t.ID = s.ItemID
    // WHERE s.GroupID = \'%1$s\'
    // AND s.ObjectID=\'projects\'
    // GROUP BY s.ItemID ORDER BY s.AccessID DESC');

    define('CU_SQL_SESSIONS_COUNT',
    'SELECT COUNT(ID) FROM sysSessions');

    define('CU_SQL_SESSIONS_CLEAR',
    'DELETE FROM sysSessions WHERE Timeout < %s');

    define('CU_SQL_SESSIONS_GETID',
    'SELECT ID FROM sysSessions WHERE ID = \'%s\'');

    define('CU_SQL_SESSIONS_HEARTBEAT',
    'UPDATE sysSessions SET Timeout = \'%s\' WHERE ID = \'%s\'');

    define('CU_SQL_SESSIONS_DATA',
    'SELECT Data FROM sysSessions WHERE ID = \'%s\'');

    define('CU_SQL_SESSIONS_UPDATE',
    'UPDATE sysSessions SET Data = \'%s\' WHERE ID = \'%s\'');

    define('CU_SQL_SESSIONS_CREATE',
    'INSERT INTO sysSessions (ID, Timeout) VALUES (\'%s\', \'%s\')');
    ?>


    Originally Posted by jcarouth
    Yes you do have a similar problem. In fact, I would say it is almost identical. And the answer is still the same: get rid of the "list join" or "comma join" syntax.

    And why in the name of a deity you defining the query as a constant?

    So
    Code:
    select t1.id
       , t2.some_column
    from some_table as t1, another_table as t2
    where t1.some_column = t2.some_column
    should be
    Code:
    select t1.id
       , t2.some_column
    from some_table as t1
    inner
       join another_table as t2
          on t1.some_column = t2.some_column
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    change this --

    FROM sysGroupPermissions AS g, tblUsers_Groups AS t
    LEFT JOIN sysUserPermissions AS u ON ( u.UserID = '2'
    AND u.ItemID = g.ItemID )
    WHERE t.UserID =2
    AND t.GroupID = g.GroupID

    to this --

    FROM sysGroupPermissions AS g
    INNER JOIN tblUsers_Groups AS t
    ON t.GroupID = g.GroupID
    LEFT JOIN sysUserPermissions AS u ON ( u.UserID = '2'
    AND u.ItemID = g.ItemID )
    WHERE t.UserID =2

    see http://dev.mysql.com/doc/refman/5.0/en/join.html

    basically, you had

    ... FROM g, t LEFT JOIN u ON u.x = g.y

    however, the comma has lower precedence than JOIN

    therefore, your query was equivalent to

    ... FROM g, ( t LEFT JOIN u ON u.x = g.y )

    and of course that's not valid, is it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    3
    Rep Power
    0
    thanks a lot for all the help, i will try that
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    5
    Rep Power
    0
    Originally Posted by sylphdesign
    thanks a lot for all the help, i will try that
    hi guys

    i have a similar problem, but just cant seem to get it working (spent ages on it !)

    would be really glad if someone could help ?

    my code is:

    select p.propertyid, p.fulladdress, p.shortdescr, pb.bednum, pt.title, l.title, d.fileid,p.price

    from property p, property_bed pb, property_type pt, property_location l left join property_data d on p.propertyid = d.propertyid and d.type='A'

    where pb.bedid = p.bedid and pt.typeid = p.typeid
    and l.locationid = p.locationid and IFNULL(p.price,0) <= 1.0E+8 order by 8

    Any help would be really appreciated

    A
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    adamf, did you actually read this thread?

    because the answer to your situation is clearly given
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    5
    Rep Power
    0
    Originally Posted by r937
    adamf, did you actually read this thread?

    because the answer to your situation is clearly given
    I did read the thread. sincerely sorry, I am an MSSQL person, and am pretty new to MYSQL, and am just trying to fix something for a friend, and have been trying to get my head round it

    I will continue my learning curve !
  28. #15
  29. Never give up
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Sep 2005
    Location
    College Station, TX, USA
    Posts
    2,625
    Rep Power
    809
    This should help:
    Code:
    FROM table1 , table2 LEFT JOIN table3...WHERE table1.somecolumn = table2.somecolumn
    should be written as
    Code:
    FROM table1
    INNER
       JOIN table2
          ON table1.somecolumn = table2.somecolumn
    LEFT OUTER
       JOIN table3
          ON whatever = whateverelse
    because of the operator precedence of the comman join (list join syntax) being changed.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo