Thread: Unknown column

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Unknown column


    Hello im try in to pass lt.teamid in to the next selection but i geting and error
    #1054 - Unknown column 'lm.teamid' in 'where clause'
    is it not possible?

    Code:

    PHP Code:
    SELECT lm.teamidlt.teamname FROM (SELECT COUNT(matchtime) as tot FROM live_matches WHERE hometeam lm.teamid OR awayteam lm.teamid AND status '4') as h, (SELECT `hometeam` AS teamidtournamentid FROM live_matches UNION SELECT `awayteam` AS teamidtournamentid FROM live_matches) as lmlive_teams AS lt WHERE lm.teamid lt.teamid AND lm.tournamentid '129568' 
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by heo445
    Hello im try in to pass lt.teamid in to the next selection
    could you please explain what you're trying to do

    "pass lt.teamid in to the next selection" doesn't make sense

    by the way, PLEASE format your queries better

    sql on a single line requiring a horizontal scroll is forum-hostile

    Code:
    SELECT lm.teamid
         , lt.teamname 
      FROM ( SELECT COUNT(matchtime) as tot 
               FROM live_matches 
              WHERE hometeam = lm.teamid 
                 OR awayteam = lm.teamid 
                AND status = '4' ) as h
         , ( SELECT `hometeam` AS teamid
                  , tournamentid 
               FROM live_matches 
             UNION 
             SELECT `awayteam` AS teamid
                  , tournamentid 
               FROM live_matches ) as lm
         , live_teams AS lt 
     WHERE lm.teamid = lt.teamid 
       AND lm.tournamentid = '129568'
    the reason you got an error is because the subquery in the FROM clause cannot reference a different table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    lm is the name of the table of results from your nested select. you can't use that name within the earlier nested select.

    you are also not using most of the results and names from this query, so it's very unclear what it is you are trying to do

IMN logo majestic logo threadwatch logo seochat tools logo