AidanLee has asked for the wisdom of the Perl Monks concerning the following question:

I'll be the first to admit this is a bit off-topic, and I'd happily listen to any suggestions of where else I should take this question (as long as it doesn't hurt :) )

My perl code has generated the following query, and all looks good to me:

select csGroups.ID as groupid,csUsers.UserName as username,csUsers.ID as id,csGroups_lang.Title as grouptitle from csGroups_lang,csUsers,csGroups,csUsers_Groups where csGroups.ID = csGroups_lang.ID and csUsers.ID = csUsers_Groups.UserID and csGroups.ID = csUsers_Groups.GroupID and (id IN (1,2)) order by username asc limit 0,10

But the MySQL database i've connected to via dbi doesn't like it. It's reason:

Unknown column 'csUsers_Groups.UserID' in 'where clause'

[download]

I have verified that csUsers_Groups.UserID does indeed exist. Do any of you Perl/DB gurus know if you have to select a column from a table in order to be allowed to use the column in your where clause?

Replies are listed 'Best First'.
Re: generated SQL statement problem (OT)
by mr.dunstan (Monk) on Jun 22, 2001 at 21:18 UTC
    I'm pretty certain you don't have to select a column in order to use it in a join like that. I wonder if a) the column name is indeed incorrect or b) you might want to add alias names to your tables like:

    ... from csGroups_lang cl, csUsers cu, csGroups cg, csUsers_Groups cug, where ... cug.UserID = cg.ID ...

    Hope this helps, I know this isn't a complete answer ...

    mr.dunstan
    "There are only 35 chambers, there is no 36th."
    "I know, I want to create a new one... " - Shaolin Master Killer
[reply]
Re: generated SQL statement problem (OT)
by MrCromeDome (Deacon) on Jun 22, 2001 at 21:19 UTC
    Well, I have very limited experience with MySQL, but, in general (i.e., with the other 5 or 6 DBMSes I've used), you do not have to SELECT a column in order to use it in the WHERE clause. It seems very, very odd to me to think that MySQL would have that kind of restriction.

    I'll check over your script in a little more detail and see if I spot anything.

    MrCromeDome

    Update #2: I deleted my original update as I was totally mistaken. . . I don't use SQL aliasing much, and inadvertantly took you down a wrong path. Albannach pointed it out to me, so vote his nodes up, not mine ;)

    He pointed out that you can't use column aliases at all in a WHERE clause. I know your error doesn't specifically seem to relate to an aliasing issue. . . but eliminating the alias never hurts ;) Perhaps try: 

    select csGroups.ID as groupid,csUsers.UserName as username,csUsers.ID 
+as id,csGroups_lang.Title as grouptitle from csGroups_lang,csUsers,cs
+Groups,csUsers_Groups where csGroups.ID = csGroups_lang.ID and csUser
+s.ID = csUsers_Groups.UserID and csGroups.ID = csUsers_Groups.GroupID
+ and (csUsers.ID IN (1,2)) order by username asc limit 0,10

    [download]
    and see what happens. Hope this helps!
    MrCromeDome
[reply]
[d/l]

      I modified my sql statement to this:

      select csGroups.ID as groupid,csUsers.UserName as username, csGroups_lang.Title as grouptitle,csUsers.ID as uid from csGroups_lang,csUsers,csGroups,csUsers_Groups where csGroups.ID = csGroups_lang.ID and csUsers.ID = csUsers_Groups.UserID and csGroups.ID = csUsers_Groups.GroupID and (csUsers.ID IN (1,2)) order by username asc limit 0,10

      and got the same error through my script:

      Unknown column 'csUsers_Groups.UserID' in 'where clause'

      however it ran cleanly from the mysql command line. So something between the database and my script (the dbd driver?) doesn't like it. Odd....

[reply]
[d/l]
Re: generated SQL statement problem (OT)
by voyager (Friar) on Jun 22, 2001 at 22:11 UTC
    As others have noted, it looks ok. You can have columns in WHERE not referenced in the results set. Note that most RDBSMs won't allow a column reference in a sort that isn't in the select. But that's not your problem.

    Two suggestions:

    1. Paste the generated SQL into an ISQL client and see if it runs. If so, there's a DBI or other Perl / MySql issue.
    2. If not, then start with a very simle query and add pieces one at a time to see where it breaks.
    hth
[reply]
      Ok, a couple have mentioned it, but what i was trying to ask is does a TABLE need to be represented by one of its columns in the columns selected to be useable in the where clause.
[reply]
Re: generated SQL statement problem (OT)
by scain (Curate) on Jun 22, 2001 at 21:20 UTC
    All I can say is "Huh, that's funny." It looks like perfectly good SQL to me. You've tried this command at an SQL prompt? What about trimming it down, only querying on that table, for example.

    Anyway, in general, you don't have to select a column to use it in the where clause.

    Good luck,
    Scott

[reply]
Re: generated SQL statement problem (OT)
by VSarkiss (Monsignor) on Jun 23, 2001 at 01:26 UTC
    Well, this is kinda late, but... Have you checked the obvious? Does the csUsers_Groups table have a column named UserID? If I take the error message at face value, that's what it's saying.

    HTH

[reply]
[d/l]
[select]
      I have verified that csUsers_Groups.UserID does indeed exist
[reply]
Re: generated SQL statement problem (OT)
by sierrathedog04 (Hermit) on Jun 22, 2001 at 22:33 UTC
    select csGroups.ID as groupid,csUsers.UserName as username,csUsers.ID as id,csGroups_lang.Title as grouptitle from csGroups_lang,csUsers,csGroups,csUsers_Groups where csGroups.ID = csGroups_lang.ID and csUsers.ID = csUsers_Groups.UserID and csGroups.ID = csUsers_Groups.GroupID and (id IN (1,2)) order by username asc limit 0,10
    My Informix database does not understand the limit 0,10 clause. Indeed, I have been doing SQL for about ten years and I do not recognize that syntax.

    I suspect that the mySQL installation that you are using may not recognize it either.

[reply]
      That is actually legal in MySQL:

      • limit x,y - take the next y rows starting at row x (first row is row 0)
      • limit x - take the first x rows, equivalent to limit 0,x

        --
        I'd like to be able to assign to an luser

[reply]
[d/l]
[select]
      I have never seen that either.. I'm 'guessing' they want the top 10 records? If so.. does mySql support a function similar to TOP?
[reply]
Re: generated SQL statement problem (OT)
by Anonymous Monk on Jun 25, 2001 at 18:36 UTC
    Have you swapped csGroups and csUsers_Groups in your from clause? Maybe DBI works funny with to many tables?
[reply]

Back to Seekers of Perl Wisdom