select sql query(resolved thanks)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
iris
Forum Newbie
Posts: 18
Joined: Wed Sep 10, 2008 4:25 am

select sql query(resolved thanks)

Post 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
Last edited by iris on Fri Sep 26, 2008 9:28 am, edited 1 time in total.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: select sql query

Post by jayshields »

Try using a longer name, such as "total". Also try using backticks around the names.
iris
Forum Newbie
Posts: 18
Joined: Wed Sep 10, 2008 4:25 am

Re: select sql query

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: select sql query

Post 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
iris
Forum Newbie
Posts: 18
Joined: Wed Sep 10, 2008 4:25 am

Re: select sql query

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: select sql query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
iris
Forum Newbie
Posts: 18
Joined: Wed Sep 10, 2008 4:25 am

Re: select sql query

Post 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
Post Reply