Having problems with subqueries

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Daniel Elliott
    #1

    Having problems with subqueries

    Hello,

    I was wondering if anyone would be able to help me with a problem I'm
    having. I'm trying to use the following query:

    SELECT Distinct c.site_id
    FROM campsite c
    WHERE c.site_id NOT IN (SELECT cs.site_id
    FROM campsite_status cs
    WHERE c.site_id = cs.site_id
    AND cs.status_date = '2004-06-22')

    Basically I'm trying to get a listing of the campsites that are not in
    use on the specified day. But I'm getting the following error from
    MySQL Command Center.

    [root@localhost: 3306] 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 cs.site_id
    FROM campsite_statu

    Anyone know why? I'd really appreciate the help, I can seem to get
    any subqueries to work.

    Thanks,

    Dan
    Tags: None
    • Tony
      #2
      Re: Having problems with subqueries

      Daniel Elliott wrote:

      : Hello,
      :
      : I was wondering if anyone would be able to help me with a problem I'm
      : having. I'm trying to use the following query:
      :
      : SELECT Distinct c.site_id
      : FROM campsite c
      : WHERE c.site_id NOT IN (SELECT cs.site_id
      : FROM campsite_status cs
      : WHERE c.site_id = cs.site_id
      : AND cs.status_date = '2004-06-22')
      :
      : Basically I'm trying to get a listing of the campsites that are not in
      : use on the specified day. But I'm getting the following error from
      : MySQL Command Center.
      :
      : [root@localhost: 3306] 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 cs.site_id
      : FROM campsite_statu
      :
      : Anyone know why? I'd really appreciate the help, I can seem to get
      : any subqueries to work.
      :
      : Thanks,
      :
      : Dan



      Hi Dan,

      The error message from MySQL is likely giving you a clue.

      "Check the manual that corresponds to your MySQL server version for the
      right syntax to use near 'SELECT cs.site_id FROM ............."

      Are you using MySQL 4.1?
      fw_error_www
      http://dev.mysql.com/doc/mysql/en/Subqueries.html


      "For MySQL versions prior to 4.1, most subqueries can be successfully
      rewritten using joins and other methods."
      fw_error_www
      http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html


      ..


        Comment

        • Tony
          #3
          Re: Having problems with subqueries

          Daniel Elliott wrote:

          : Hello,
          :
          : I was wondering if anyone would be able to help me with a problem I'm
          : having. I'm trying to use the following query:
          :
          : SELECT Distinct c.site_id
          : FROM campsite c
          : WHERE c.site_id NOT IN (SELECT cs.site_id
          : FROM campsite_status cs
          : WHERE c.site_id = cs.site_id
          : AND cs.status_date = '2004-06-22')
          :
          : Basically I'm trying to get a listing of the campsites that are not in
          : use on the specified day. But I'm getting the following error from
          : MySQL Command Center.
          :
          : [root@localhost: 3306] 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 cs.site_id
          : FROM campsite_statu
          :
          : Anyone know why? I'd really appreciate the help, I can seem to get
          : any subqueries to work.
          :
          : Thanks,
          :
          : Dan



          Hi Dan,

          The error message from MySQL is likely giving you a clue.

          "Check the manual that corresponds to your MySQL server version for the
          right syntax to use near 'SELECT cs.site_id FROM ............."

          Are you using MySQL 4.1?
          fw_error_www
          http://dev.mysql.com/doc/mysql/en/Subqueries.html


          "For MySQL versions prior to 4.1, most subqueries can be successfully
          rewritten using joins and other methods."
          fw_error_www
          http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html


          ..


            Comment

            • Daniel Elliott
              #4
              Re: Having problems with subqueries

              "Tony" <nospam@nospam. net> wrote in message news:<xiTjc.104 $E17.54@newsfe2-gui.server.ntli .net>...[color=blue]
              > Daniel Elliott wrote:
              >
              > : Hello,
              > :
              > : I was wondering if anyone would be able to help me with a problem I'm
              > : having. I'm trying to use the following query:
              > :
              > : SELECT Distinct c.site_id
              > : FROM campsite c
              > : WHERE c.site_id NOT IN (SELECT cs.site_id
              > : FROM campsite_status cs
              > : WHERE c.site_id = cs.site_id
              > : AND cs.status_date = '2004-06-22')
              > :
              > : Basically I'm trying to get a listing of the campsites that are not in
              > : use on the specified day. But I'm getting the following error from
              > : MySQL Command Center.
              > :
              > : [root@localhost: 3306] 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 cs.site_id
              > : FROM campsite_statu
              > :
              > : Anyone know why? I'd really appreciate the help, I can seem to get
              > : any subqueries to work.
              > :
              > : Thanks,
              > :
              > : Dan
              >
              >
              >
              > Hi Dan,
              >
              > The error message from MySQL is likely giving you a clue.
              >
              > "Check the manual that corresponds to your MySQL server version for the
              > right syntax to use near 'SELECT cs.site_id FROM ............."
              >
              > Are you using MySQL 4.1?
              > http://dev.mysql.com/doc/mysql/en/Subqueries.html
              >
              > "For MySQL versions prior to 4.1, most subqueries can be successfully
              > rewritten using joins and other methods."
              > http://dev.mysql.com/doc/mysql/en/Re...ubqueries.html
              >
              > .[/color]

              I found a workaround for this, but I can't seem to get a "where x IN
              ('asdfa', 'adf')" type query to work. Is this also due to using 4.0?

                Comment

                • Daniel Elliott
                  #5
                  Re: Having problems with subqueries

                  "Tony" <nospam@nospam. net> wrote in message news:<xiTjc.104 $E17.54@newsfe2-gui.server.ntli .net>...[color=blue]
                  > Daniel Elliott wrote:
                  >
                  > : Hello,
                  > :
                  > : I was wondering if anyone would be able to help me with a problem I'm
                  > : having. I'm trying to use the following query:
                  > :
                  > : SELECT Distinct c.site_id
                  > : FROM campsite c
                  > : WHERE c.site_id NOT IN (SELECT cs.site_id
                  > : FROM campsite_status cs
                  > : WHERE c.site_id = cs.site_id
                  > : AND cs.status_date = '2004-06-22')
                  > :
                  > : Basically I'm trying to get a listing of the campsites that are not in
                  > : use on the specified day. But I'm getting the following error from
                  > : MySQL Command Center.
                  > :
                  > : [root@localhost: 3306] 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 cs.site_id
                  > : FROM campsite_statu
                  > :
                  > : Anyone know why? I'd really appreciate the help, I can seem to get
                  > : any subqueries to work.
                  > :
                  > : Thanks,
                  > :
                  > : Dan
                  >
                  >
                  >
                  > Hi Dan,
                  >
                  > The error message from MySQL is likely giving you a clue.
                  >
                  > "Check the manual that corresponds to your MySQL server version for the
                  > right syntax to use near 'SELECT cs.site_id FROM ............."
                  >
                  > Are you using MySQL 4.1?
                  > http://dev.mysql.com/doc/mysql/en/Subqueries.html
                  >
                  > "For MySQL versions prior to 4.1, most subqueries can be successfully
                  > rewritten using joins and other methods."
                  > http://dev.mysql.com/doc/mysql/en/Re...ubqueries.html
                  >
                  > .[/color]

                  I found a workaround for this, but I can't seem to get a "where x IN
                  ('asdfa', 'adf')" type query to work. Is this also due to using 4.0?

                    Comment

                    • Siemel Naran
                      #6
                      Re: Having problems with subqueries

                      "Tony" <nospam@nospam. net> wrote in message news:xiTjc.104$ E17.54@newsfe2-[color=blue]
                      > Daniel Elliott wrote:[/color]
                      [color=blue]
                      > : SELECT Distinct c.site_id
                      > : FROM campsite c
                      > : WHERE c.site_id NOT IN (SELECT cs.site_id
                      > : FROM campsite_status cs
                      > : WHERE c.site_id = cs.site_id
                      > : AND cs.status_date = '2004-06-22')[/color]
                      [color=blue]
                      > "Check the manual that corresponds to your MySQL server version for the
                      > right syntax to use near 'SELECT cs.site_id FROM ............."
                      >
                      > Are you using MySQL 4.1?
                      > http://dev.mysql.com/doc/mysql/en/Subqueries.html[/color]

                      These issues are similar to my thread "find all products with maximum
                      version". The manual mentions lots of ideas, but the database does not
                      support these. What is the solution? What version should we upgrade to?
                      The MySql website only recommends 4.0.


                        Comment

                        • Siemel Naran
                          #7
                          Re: Having problems with subqueries

                          "Tony" <nospam@nospam. net> wrote in message news:xiTjc.104$ E17.54@newsfe2-[color=blue]
                          > Daniel Elliott wrote:[/color]
                          [color=blue]
                          > : SELECT Distinct c.site_id
                          > : FROM campsite c
                          > : WHERE c.site_id NOT IN (SELECT cs.site_id
                          > : FROM campsite_status cs
                          > : WHERE c.site_id = cs.site_id
                          > : AND cs.status_date = '2004-06-22')[/color]
                          [color=blue]
                          > "Check the manual that corresponds to your MySQL server version for the
                          > right syntax to use near 'SELECT cs.site_id FROM ............."
                          >
                          > Are you using MySQL 4.1?
                          > http://dev.mysql.com/doc/mysql/en/Subqueries.html[/color]

                          These issues are similar to my thread "find all products with maximum
                          version". The manual mentions lots of ideas, but the database does not
                          support these. What is the solution? What version should we upgrade to?
                          The MySql website only recommends 4.0.


                            Comment

                            Previous template Next
                            Working...