hi guys, im a newbie to mysql and what im trying to do is...

i have two tables in the mysql database intranet, and the first table is called taskstat, and the second is todos

taskstat:
this is just a simplified version of the tables, it originaly contains lots of other information..

what i want to do is, have it so when i display the information about the "todos" table, when it reads the status_id from todo, it will display it as open or closed because it would find that info out from taskstat table..

currently we have this query:

<czdataset ittasks1 intranet todos query "select todo_id,item,description,status_id,creator_id,assi gned_id,date_created,date_fixed,first(select status from taskstat where taskstat.status_id=todos.status_id) as status from todos where status_id = '1'" "todo_id,item,description,status_id,creator_id,ass igned_id,date_created,date_fixed" "*:*" >

so that when i put "status" as a field, it will display open or closed

however, with this i get the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'select'.
/it/archtml.asp, line 493

i would really appreciate any help on this, thanks!

You are using a version of mysql that doesn't support sub-selects. You need to rewrite the query as a join. it will look like:
Although, in your query, you have the restriction that only the open queries are shown (status_id = 1), so you don't need a join, you can just specify 'open' for status. If you want the join to do any good, leave the status_id requirement out.

actually the restriction i have for open queries (status_id = 1) i had in there from before but i left in there while testing.. i don't understand how u have

WHERE ... todos.status_id = taskstat.statusid

do i need an "AS status" after that? because i want it so when i have a field and put status in there, it will display the actual status such as (OPEN, CLOSED) etc... ?

also, under SELECT:

do i have to have it like SELECT taskstat.status_id, todos.status_id because when i only do status_id it gives me ambiguous column name im guessing because it doesnt know which table to select the status_id from?


right now what i have is:

however, the error i get now is:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/it/archtml.asp, line 504

Also, I'm not sure what the strings after the bold code is supposed to be?

thanks for the help

I don't know what that is after the bold text. For help with that, you might want to ask for help with ASP. Also, I just caught the error message in your first post. You are using MS SQL Server, so you might want to post this in the SQL Server forum instead of the MySQL forum. They can help you with your query. SQL Server supports a richer set of capabilities in its implementation of SQL (Transact SQL) than MySQL does so someone versed in that could give you a better answer than just the ANSI version you might get here.

Just a thought, you might want to separate the query from the ASP code first, so that it is more readable.

thanks for all the help before, i was able to figure it out yesterday