Page 1 of 1

select sql query(resolved thanks)

Posted: Wed Sep 24, 2008 4:13 am
by iris
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

Re: select sql query

Posted: Wed Sep 24, 2008 5:17 am
by jayshields
Try using a longer name, such as "total". Also try using backticks around the names.

Re: select sql query

Posted: Wed Sep 24, 2008 5:33 am
by iris
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

Re: select sql query

Posted: Wed Sep 24, 2008 1:02 pm
by califdon
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_ID

Re: select sql query

Posted: Thu Sep 25, 2008 4:33 am
by iris
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

Re: select sql query

Posted: Thu Sep 25, 2008 7:07 am
by VladSun
Use HAVING instead of WHERE for aggregate functions ;)

http://dev.mysql.com/doc/refman/5.0/en/select.html
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;
In other words - HAVING is equal to WHERE but it is applied for results produced by GROUP BY.

Re: select sql query

Posted: Fri Sep 26, 2008 3:01 am
by iris
:lol:
VladSun thanks a lot cos it worked with the HAVING clause and to every other persons that contributed u re Good guys