470,591 Members | 2,174 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

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
Jul 20 '05 #1
6 1880
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

..
Jul 20 '05 #2
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

..
Jul 20 '05 #3
"Tony" <no****@nospam.net> wrote in message news:<xi**************@newsfe2-gui.server.ntli.net>...
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

.


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?
Jul 20 '05 #4
"Tony" <no****@nospam.net> wrote in message news:<xi**************@newsfe2-gui.server.ntli.net>...
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

.


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?
Jul 20 '05 #5
"Tony" <no****@nospam.net> wrote in message news:xiTjc.104$E17.54@newsfe2-
Daniel Elliott wrote: : 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') "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


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.
Jul 20 '05 #6
"Tony" <no****@nospam.net> wrote in message news:xiTjc.104$E17.54@newsfe2-
Daniel Elliott wrote: : 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') "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


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.
Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by pete | last post: by
2 posts views Thread by Kevin | last post: by
2 posts views Thread by orin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.