SQL Error

[lead2gold]

[code]
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
[/code]

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?

[fenway]

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.

[lead2gold]

[!--quoteo(post=376163:date=May 22 2006, 05:50 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 22 2006, 05:50 PM) [snapback]376163[/snapback][/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