By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

Having problems with subqueries

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
"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

P: n/a
"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

P: n/a
"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.