From: Barry (b_j_ambrosehotmail.com)
Date: Mon Oct 03 2005 - 16:12:33 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

  ----- Original Message -----
  From: SGreenunimin.com
  To: Barry
  Cc: mysqllists.mysql.com
  Sent: Monday, October 03, 2005 9:49 PM
  Subject: Re: Query Trouble!

  "Barry" <b_j_ambrosehotmail.com> wrote on 10/03/2005 04:38:48 PM:

> I'm new to MySQL and am using version 4-1-12a on my development
> machine locally this query works just fine:
>
> SELECT * FROM actor
> WHERE id IN
> ( SELECT id FROM episode_cast
> WHERE episode_number =001)
>
> but when I try to run it from the web server which is running version
> 4.0.24-standard I get an error:
>
> #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 id from episode_cast WHERE episode_number = 001
>
> I put this together from a book that's at least 2 years old so I
> wouldn't have thought that it was because of different versions, but I
> could well be wrong!
>
> Any help would be much appreciated.
>

  Well, Barry, the book was written towards 4.1 as that is the first version with subqueries. You will need to convert your SQL into the JOIN-form like this:

  SELECT a.*
  FROM actor a
  INNER JOIN episode_cast e
          on e.id = a.id
  WHERE e.episode_number = 001;

  That will work on all versions :-)

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine

  Thanks for that Shawn it works a treat.

   I have just spent the last three hours reading about joins and subquerys in the manual and I'm still no clearer on how they work..

  Barry

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]