Results 1 to 4 of 4

Thread: Joining Tables

  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: Joining Tables

    Is it possible to join a single row (if any) from another table? even though there is more than one row in the joining table

    Code:
    tbl_task                 tbl_attachments                         
    id    name               id    task_id    file
    1     Fix Bug            1     1          screenshot.jpg
    2     Test               2     1          trace_output.txt
    3     Upload
    it doesn't matter which attachment is shown, but as long as one is

    Code:
    tbl_task_attachment
    id   name          file
    1    Fix Bug       screenshot.jpg
    2    Test          NULL
    3    Upload        NULL

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t.id
         , t.name
         , a.file
      from tbl_task as t
    left outer
      join tbl_attachments as a
        on a.task_id = t.id
       and a.id
         = ( select max(id)
               from tbl_attachments
              where task_id = t.id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    No luck, here's the query I'm using...

    Code:
    SELECTtbl_subtask.id, tbl_subtask.name, tbl_attachment.attachment
    FROM tbl_task
    LEFT JOIN tbl_attachment ON tbl_attachment.subtask_id = tbl_subtask.id
    AND tbl_attachment.id = (
    SELECT max( id )
    FROM tbl_attachment
    WHERE tbl_attachment.subtask_id = tbl_subtask.id )
    and I get the following error ...

    Code:
    #1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(id)
               from tbl_attachment
              where

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    upgrade to 4.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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