Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: error in your SQL syntax near 'SELECT ... (was "Basic MySQl Command")

    Hi,
    Im new to Mysql but have been used to using SQL syntax for SQL server. I have a problem trying to run the following code. The code is included with the error.

    Error

    SQL-query :

    SELECT p.id, p.name
    FROM partners p
    WHERE p.id
    IN (


    SELECT partner_id AS id
    FROM partner_area
    WHERE area_id = 10
    )
    LIMIT 0 , 30

    MySQL said:


    You have an error in your SQL syntax near 'SELECT partner_id AS id
    FROM partner_area
    WHERE area_id = 10 ) LIMIT 0, 30' at line 5


    Im sure this is a basic solution but cant get my head around it at the moment.
    I dont understand the error as I have run the code inside the inner select and it works find. Can anyone tell me where the error points to.

    I am running this code within a control panel of a web hosting site to test on the database. I want to then apply this code to my site.

    Please help
    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to upgrade to version 4.1, to support the use of subqueries

    either that, or re-write your queries so that they don't use subqueries
    Code:
    select p.id
         , p.name
      from partners as p
    inner
      join partner_area as a 
        on p.id
         = a.partner_id 
       and a.area_id 
         = 10
    limit 0, 30
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2005
    Posts
    3
    ok thats great, thanks,
    but what happens when I would like to use a not in subquery command. For example.

    SELECT p.id, p.name
    FROM partners p
    WHERE p.id NOT
    IN (
    SELECT partner_id AS id
    FROM partner_area
    WHERE area_id = 10
    )

    I would be grateful for this solution aswell.
    Kind regards
    Jimfl

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    time to take a basic tutorial on joins, i'm guessing
    Code:
    select p.id
         , p.name
      from partners as p
    left outer
      join partner_area as a 
        on p.id
         = a.partner_id 
       and a.area_id 
         = 10
     where a.partner_id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2005
    Posts
    3
    ok thanks , most of the work I have done involves the inner joins.
    Cheers again

Posting Permissions

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