Brian W Posted July 22, 2009 Share Posted July 22, 2009 I aliased the results of a subquery and need to tell the query to use the resulting value in the WHERE clause. MySQL Error: Unknown column 'Tasks' in 'where clause' SELECT p.project, p.id as pid, p.acronym, p.Client as cid, c.client, (SELECT COUNT(t.Task) FROM tasks t WHERE t.pid = p.id GROUP BY t.pid) as Tasks FROM projects p LEFT JOIN clients c ON p.Client = c.id WHERE Tasks > 0 ORDER BY project ASC Quote Link to comment Share on other sites More sharing options...
kickstart Posted July 22, 2009 Share Posted July 22, 2009 Hi Alias the column name as well SELECT p.project, p.id as pid, p.acronym, p.Client as cid, c.client, (SELECT COUNT(t.Task) AS Fred FROM tasks t WHERE t.pid = p.id GROUP BY t.pid) as Tasks FROM projects p LEFT JOIN clients c ON p.Client = c.id WHERE Tasks.Fred > 0 ORDER BY project ASC All the best Keith Quote Link to comment Share on other sites More sharing options...
Maq Posted July 22, 2009 Share Posted July 22, 2009 I fail to see how that sub query would effect the result. It has no relation with the main query. And if you wanted to select the Tasks that occur at least once then use a HAVING clause in the sub query. Quote Link to comment Share on other sites More sharing options...
Brian W Posted July 22, 2009 Author Share Posted July 22, 2009 kickstart, that idea didn't work. It seems to me that whatever is inside the sub-query can't be used in the sup-query's WHERE clause. I have given the user the option to hide "projects" in which no tasks are open. How can I use HAVING? I need to not return the project if there are no tasks. Before I began using the subquery, I was using a LEFT JOIN with the project table on the left but it would not return projects that had no tasks, it needs to be an optional filter. Thanks guys Quote Link to comment Share on other sites More sharing options...
kickstart Posted July 22, 2009 Share Posted July 22, 2009 Hi Sorry, I misread the issue. Maq is correct that you can use a HAVING clause (like a WHERE, but done later), or you could use an extra outer join. Something like (probably some typos):- SELECT p.project, p.id as pid, p.acronym, p.Client as cid, c.client, (SELECT COUNT(t.Task) AS Fred FROM tasks t WHERE t.pid = p.id GROUP BY t.pid) as Tasks FROM projects p LEFT OUTER JOIN clients c ON p.Client = c.id LEFT OUTER JOIN (SELECT pid, COUNT(Task) AS Fred FROM tasks t GROUP BY pid) t ON p.id =t.pid WHERE t.Fred > 0 ORDER BY project ASC All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.