Link to home
Create AccountLog in
Avatar of stargateatlantis
stargateatlantis

asked on

Not sure having problems getting this SQL Statement working

I am not sure but I get the following error

Unknown column 'tsk.taskid' in 'on clause'


Here is the SQL statement


SELECT *,count(requestID) as requestCount FROM tasks tsk,tasks_categories,tasks_gallery,measure,auth_user left outer join task_user_request tskr on tsk.taskid=tskr.task_id where measure_id=measureid and userID=id and catid=cat_id and photoid=photo_id AND tsk.taskid=tskr.task_id GROUP BY userID,url
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

I do not recommend mixing join style in the same SQL ... i.e. joins in the where plus ANSI format (left outer).
I suggest you make the whole statement ANSI format to avoid confusion and problems (like you also having tsk.taskid=tskr.task_id in the where clause which will invalidate the outer join).
So use ANSI for all joins ... you will need to fix the ON clauses
SELECT *,count(requestID) as requestCount
FROM tasks tsk
JOIN tasks_categories ON ??
JOIN tasks_gallery ON ??
JOIN measure ON ??
JOIN auth_user ON ??
left outer join task_user_request tskr on tsk.taskid=tskr.task_id
GROUP BY userID,url
Check if table "tasks" has column taskid. May be it is named "id"?

tsk.id=tskr.task_id

then just good practice, it saves you time on a long run
try to specify your join column in "from" not where, this way your query will be more understandable
and always qualify column names  with table name
here is template - I am just guessing your joins and which column in with table

If you rewrite you query like this you may see what is wrong

SELECT tsk.userID,tc.url,count(requestID) as requestCount
FROM tasks tsk
join tasks_categories tc on tc.someColumn1= tsk.someColumn1 and ....
join tasks_gallery tg on tg.someColumn2 = tc.someColumn2 and ...
join measure m on m.someColumn3=tg.someColumn3 and ...
join auth_user au on tsk.userID = au.userId
left outer join task_user_request tskr on tsk.taskid=tskr.task_id
where tg.measure_id=m.measureid and tsk.userID=au.id and tsk.catid=m.cat_id and photoid=photo_id AND tsk.taskid=tskr.task_id
GROUP BY userID,url
Avatar of stargateatlantis
stargateatlantis

ASKER

The end result was to get the extra fields with values also to count the number of requests.  so the question is what is the question marks for.  

This query works below

SELECT *,count(requestID) as requestCount FROM tasks tsk left outer join task_user_request tskr on tsk.taskid=tskr.task_id GROUP BY userID,url

But I was adding the extra tables because I require the extra fields within the tables
the question marks where for the missing join predicates that you would need to add.  As I am not familiar with your tables and as you have not prefixed the column names with a synonym or tablename - I cannot be sure which fields join which tables.

A complete GUESS that could well be wrong, based on the field names being a little related to the table names is:

SELECT *,count(requestID) as requestCount
FROM tasks tsk
JOIN tasks_categories ON catid=cat_id
JOIN tasks_gallery ON photoid=photo_id
JOIN measure ON measure_id=measureid
JOIN auth_user ON userID=id
left outer join task_user_request tskr on tsk.taskid=tskr.task_id
GROUP BY userID,url
stargateatlantis:

There are two main issues here.  First, you should rewrite your query in ANSI format as suggested by 'lwadwell.'  Second, and most importantly it is extremely bad form to use 'SELECT *' in any situation, with the possible exception of quick debugging sessions - and I don't even use it there.  Even then, it may not be a good idea.  I'm betting the SELECT * is the root cause of the issue here.

First of all, what columns are you selecting and from what tables?  The SELECT * is selecting all columns from all the tables listed.  Next, in an aggregate query (GROUP BY), all columns not included in the GROUP BY statement must have an aggregate operator specified for them (MIN, MAX, AVERAGE, etc).  It appears the majority of yours do not.  Third, the SELECT * masks your data model and table properties from the experts trying to assist you.  What are the column names in the tables you are using?  We can not tell.  

Try rewriting your query in ANSI standard format and replace the SELECT * with the column names you need in the result set.  It will most likely resolve the issue.

Best regards,

AielloJ
I did that take a look at the query now

SELECT count(requestID) as requestCount, catID, name, photoPath, thumbPath, styleicon, parent_id, photoID, cat_ID, photo, measureid, measure_type, taskID, typeID, userID, title, descr, photo_id, phto, photoUrl, tsk.thinking, startDate, measure_id, expire, address, postalzip, postie, lat, lng, notes, tsk.status, paystatus, url, premium_id, id, user_name, first_name, last_name, email, password, created_on, modified_on, registration_key, reset_password_key, registration_id,count(requestID) as requestCount FROM tasks tsk
JOIN tasks_categories ON catid=cat_ID
JOIN tasks_gallery ON photoid=photo_id
JOIN measure ON measure_id=measureid
JOIN auth_user ON userID=id
left outer join task_user_request tskr ON tsk.taskid=tskr.task_id GROUP BY userID,url

The problem is it comes back saying this
Unknown column 'cat_ID' in 'on clause'
Which table or tables does the cat_ID exist in?  As we do not have descriptions of your tables it hard for us to tell.  
If the cat_ID exists in more than one of your tables in the SQL ... you need to alias the table and reference the field to that alias e.g.
JOIN tablename syn ON syn.column_name = ... etc
... actually I strongly recommend that you alias and reference via the alias for all tables and columns as matter of habit.
If the cat_ID exists only in one of the table ... double check by doing:
SELECT cat_ID from <that_table>
where <that_table> is the tablename in question.
If the cat_ID exists in no tables ... please change to right column name to satisfy the join.
Both tasks_gallery and tasks have cat_ID
stargateatlantis:

Time for part 2 of best practices when writing SQL queries: You should always use the table name or alias on all columns.  Does your error really say:

  Unknown column 'cat_ID' in 'on clause'
 
or does it mention something like:

  Ambiguous column 'cat_ID' in 'on clause'

Here's the problem:  Since cat_ID exists in both the `tasks_gallery` and `tasks` tables, SQL doesnt know what `cat_ID` column you mean to use in your join, thus an error is produced.  To correct this problem you have to put the tablename you mean to use in your join before the column name:

  JOIN tasks_categories ON catid=tasks_gallery.cat_ID

or

  JOIN tasks_categories ON catid=tasks.cat_ID

Now let's answer the question of why this practice when writing SQL queries is worth the little bit of up-front time.  If you initially wrote your query without needing to join one of the two tables containing `cat_ID`, your query would work just fine.  Later, when you need to make a simple change and modify the query to include the second table, you all of a sudden have errors (just like this one) after having made that 'simple, quick change.'  All in all, in many situations, failure to use alias names will only require you to do it later or spend a lot of time debugging.

Best regards,

AielloJ
Thanks for all the help it comes up with this

Unknown column 'cat_ID' in 'on clause'
When I do this line

JOIN tasks_categories ON catid=tasks_gallery.cat_ID

I get this error
Unknown column 'tasks_gallery.cat_ID' in 'on

When I do this line
JOIN tasks_categories ON catid=tasks.cat_ID

I get this error
Unknown column 'tasks.cat_ID' in 'on clause'
stargateatlantis:

Are you absolutely certain a column named `cat_ID` exists in the `tasks` and/or `tasks_gallery` tables?

Try running the following script and post the results here

  select * from information_schema.columns
  where table_schema = 'your_db'
  order by table_name,ordinal_position

Replace your_db in the quotes with the name of your database schema.

AielloJ
SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I made a few changes to the Query

SELECT count(requestID) as requestCount, catID, name, photoPath, thumbPath, styleicon, parent_id, tskGl.photoID, tsk.cat_ID, tskGl.photo, measureid, measure_type, taskID,userID, title, descr, photo_id, phto, photoUrl, tsk.thinking, startDate, measure_id, expire, address, postalzip, postie, lat, lng, notes, tsk.status, paystatus, url, premium_id, id, user_name, first_name, last_name, email, password, created_on, modified_on, registration_key, reset_password_key, registration_id,count(requestID) as requestCount FROM tasks tsk,tasks_gallery tskGl
JOIN tasks_categories ON catid=tskGl.cat_ID
JOIN tasks_gallery ON tskGl.photoid=tsk.photo_id
JOIN measure ON measure_id=measureid
JOIN auth_user ON userID=id
left outer join task_user_request tskr ON tsk.taskid=tskr.task_id GROUP BY userID,url

But now it says
Unknown column 'tsk.photo_id' in 'on clause'

That is the last piece of the puzzle.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
So basically I redid the query and finally got it to work based on the feedback