MySQL Unknown Coloumn with Inner join

Question

This is the first time I tried to use inner join, and I have a problem with that where clause. Unknown column 'forumtype' in 'where clause' However if I delete there where clause, then I will get the same error with the ON clause. The forum table's ID is equal to the comment's table's TOPIC_ID. Please let me know if You need any further information.

$query = mysql_query("SELECT f.id AS forumid, f.class AS forumclass, 
f.date AS forumdate, f.type AS forumtype, f.name AS forumname, f.author AS forumauthor,
f.user AS forumuser, f.url AS forumurl,
c.id AS commentid, c.user_id AS commenutuserid, c.user_name AS commentusername,
c.topic_id AS commenttopic, c.date AS commentdate
FROM ".$prefix."forum AS f
INNER JOIN ".$prefix."comment AS c
ON commenttopic = forumid
WHERE forumtype=1 AND forumclass='$c' ORDER BY commentdate DESC LIMIT $offset, $limit ") or die(mysql_error());

`f.type`. It can't see the `forumtype` alias you define in the select clause. — Millie Smith, May 08 '15 at 19:56
http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error — Millie Smith, May 08 '15 at 19:57
I've been looking so many inner join questions here, but I never thought about this, so didn't find that one. Thanks for the link though. — Phoenixy, May 08 '15 at 20:02
That's cause it has nothing to do with inner joins :). Just selects and wheres. You're welcome. — Millie Smith, May 08 '15 at 20:03

score 3 · Accepted Answer · answered May 08 '15 at 19:58

You are using alias for the column names and trying to use them in the join clause and where condition which is not allowed you need to use the column name along with the table alias name

"SELECT 
f.id AS forumid, 
f.class AS forumclass, 
f.date AS forumdate,
f.type AS forumtype, 
f.name AS forumname,
f.author AS forumauthor,
f.user AS forumuser, 
f.url AS forumurl,
c.id AS commentid, 
c.user_id AS commenutuserid, 
c.user_name AS commentusername,
c.topic_id AS commenttopic, 
c.date AS commentdate
FROM ".$prefix."forum AS f
INNER JOIN ".$prefix."comment AS c
ON c.topic_id = f.id
WHERE 
f.type=1 
AND f.class='$c' 
ORDER BY c.date DESC LIMIT $offset, $limit"

MySQL Unknown Coloumn with Inner join

1 Answers1