Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    41

    Unanswered: newbie to mysql, trying to do a query and have first(select ..)

    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:
    Code:
    status_id      status
    1                 open
    2                 closed
    
    todos:
    
    todo_id        status_id
    1                 1
    2                 2
    3                 2
    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!

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: newbie to mysql, trying to do a query and have first(select ..)

    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:
    Code:
    SELECT ..., status FROM todos, taskstat
    WHERE ... todos.status_id = taskstat.status_id;
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    41
    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:
    Code:
    <czdataset ittasks1 intranet todos query "SELECT todo_id,item,description,todos.status_id,creator_id,assigned_id,date_created,date_fixed,taskstat.status_id,status FROM todos, taskstat WHERE todos.status_id ='1' AND todos.status_id = taskstat.status_id" "todo_id, item_description,status_id,creator_id, assigned_id,date_created,date_fixed" "*;*">

    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

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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.

  5. #5
    Join Date
    Dec 2003
    Posts
    41
    thanks for all the help before, i was able to figure it out yesterday

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •