pls i ve this query i would want to run on php so that i will retrieve the followin columns based on if the counts of the task_updates.TasK_ID is equal 0 and staffname;
_____________________________________________________________________________
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND C=0 GROUP BY tasks.Task_ID
_____________________________________________________________________________
but i tried running the query first on an sql editor(DBManager) but it keeps bringin this error;
_____________________________________________________________________________
MySQL ERROR:
Unknown column 'C' in 'where clause'
QUERY:
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND C=0 GROUP BY tasks.Task_ID
select sql query(resolved thanks)
Moderator: General Moderators
select sql query(resolved thanks)
Last edited by iris on Fri Sep 26, 2008 9:28 am, edited 1 time in total.
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Re: select sql query
Try using a longer name, such as "total". Also try using backticks around the names.
Re: select sql query
i ve actualy used 'total' as d column name and it is still not workin but i dont know wat u mean by backtick. pls giv me a sample if u can.
this is the error:
MySQL ERROR:
Unknown column 'total' in 'where clause'
QUERY:
SELECT COUNT(task_updates.TasK_ID) AS total, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND total=0 GROUP BY tasks.Task_ID
this is the error:
MySQL ERROR:
Unknown column 'total' in 'where clause'
QUERY:
SELECT COUNT(task_updates.TasK_ID) AS total, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND total=0 GROUP BY tasks.Task_ID
Re: select sql query
It seems that it is looking for `C` or `total` in the 2nd table. Try this:
Code: Select all
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND [color=#FF0000]tasks.C[/color]=0 GROUP BY tasks.Task_IDRe: select sql query
i ve tried the new correction and its still not workin, pls is it possible at all in mysql to do that cos my deadline is today.
i tried this;
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND tasks.C=0 GROUP BY tasks.Task_ID
MySQL ERROR:
Unknown column 'tasks.C' in 'where clause'
QUERY:
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND tasks.C=0 GROUP BY tasks.Task_ID
and this;
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND task_updates.C=0 GROUP BY tasks.Task_ID
MySQL ERROR:
Unknown column 'task_updates.C' in 'where clause'
QUERY:
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND task_updates.C=0 GROUP BY tasks.Task_ID
i tried this;
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND tasks.C=0 GROUP BY tasks.Task_ID
MySQL ERROR:
Unknown column 'tasks.C' in 'where clause'
QUERY:
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND tasks.C=0 GROUP BY tasks.Task_ID
and this;
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND task_updates.C=0 GROUP BY tasks.Task_ID
MySQL ERROR:
Unknown column 'task_updates.C' in 'where clause'
QUERY:
SELECT COUNT(task_updates.TasK_ID) AS C, tasks.Task_ID, tasks.Task_Description, tasks.Company_ID, tasks.Staff_Name
FROM tasks LEFT JOIN task_updates ON tasks.Task_ID = task_updates.Task_ID
WHERE tasks.Staff_Name='something' AND task_updates.C=0 GROUP BY tasks.Task_ID
Re: select sql query
Use HAVING instead of WHERE for aggregate functions 
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/select.html
In other words - HAVING is equal to WHERE but it is applied for results produced by GROUP BY.The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:
SELECT user, MAX(salary) FROM users
GROUP BY user HAVING MAX(salary) > 10;
There are 10 types of people in this world, those who understand binary and those who don't
Re: select sql query
VladSun thanks a lot cos it worked with the HAVING clause and to every other persons that contributed u re Good guys