Jump to content

Archived

This topic is now archived and is closed to further replies.

lead2gold

SQL Error

Recommended Posts

[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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.