Jump to content


Photo

SQL Error


  • Please log in to reply
2 replies to this topic

#1 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 22 May 2006 - 08:52 PM

SELECT t.*, s.year,s.sid,ca.uid, ca.name_f AS capt_f, ca.name_l AS capt_l,co.uid, co.name_f AS cocapt_f, co.name_l AS cocapt_l,tier.name AS tier_name, tier.tier_id AS tier_id, dtrns.code AS div_id, dtrns.code_trans AS div_name 
FROM team AS t 
LEFT JOIN season AS s ON s.year = (SELECT MAX(s2.year) FROM season AS s2,team2season AS ts WHERE s2.sid = ts.sid AND ts.tid = t.tid GROUP BY s2.sid) 
LEFT JOIN users AS ca ON ca.uid=t.c_uid 
LEFT JOIN users AS co ON co.uid=t.co_uid 
LEFT JOIN trans AS dtrns ON dtrns.trans_name = 'division' AND dtrns.code = (SELECT tsb.division FROM team2season AS tsb WHERE tsb.sid = s.sid AND tsb.tid = t.tid) 
LEFT JOIN tier ON tier.tier_id = (SELECT tsa.tier_id FROM team2season AS tsa WHERE tsa.sid = s.sid AND tsa.tid = t.tid) 
WHERE (div_id = 'KA')  /* this is the new where clause i need to filter by */
ORDER BY s.year DESC,div_name,tier_name

delivers an error:
MySQL Query - error message Unknown column 'div_id' in 'where clause'

This issue appeared once i tried to impliment a new where clause. If i remove "dtrns.code AS div_id" and "WHERE (div_id = 'KA')" this select works perfectly.

How can i added the new where clause without getting an error?



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 May 2006 - 09:50 PM

You can't use column aliases in your where clauses, since they only get "aliased" after the result set has been obtained. Either use the underlying column name itself instead.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 23 May 2006 - 01:06 PM

[!--quoteo(post=376163:date=May 22 2006, 05:50 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 22 2006, 05:50 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
You can't use column aliases in your where clauses, since they only get "aliased" after the result set has been obtained. Either use the underlying column name itself instead.
[/quote]

Your the best fenway! thanks once again for solving my problems!
here is your sollution in action: [a href=\"http://hitech.lead2gold.org/view_teams.php\" target=\"_blank\"]http://hitech.lead2gold.org/view_teams.php[/a]
(i added the filters over the weekend)


Chris




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users