syntax error

amorphis · #1 (**permalink**) 09-11-03, 21:45

i'm trying to execute the folloowing query

SELECT StoreName, ARIAnetID, ID
FROM store_info
WHERE ID
NOT IN (SELECT ID FROM received_stores WHERE Date BETWEEN '2003-09-12 00:00:00' AND '2003-09-12 23:59:59')

but i keep on getting

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 received_stores
WHERE Date
BETWEEN '2003-09-12

if i separate the 2 queries they execute ok but together they don't. looked through all the examples and i could find and i couldn't see anything obvious i was doing wrong. help?

TIA

asherh · #2 (**permalink**) 09-13-03, 00:17

hi,

seems an obvious question, but... what version of mysql are you using?

chrs,
ash

amorphis · #3 (**permalink**) 09-13-03, 23:29

sorry, 4.0.14-nt

asherh · #4 (**permalink**) 09-14-03, 00:13

subselects are only available from version 4.1+

Mincer · #5 (**permalink**) 09-14-03, 05:06

This should do what you're after:

Code:

SELECT
  info.StoreName,
  info.ARIAnetID,
  info.ID
FROM
  store_info AS info
LEFT JOIN
  received_stores AS received
    ON
      info.ID = received.ID
WHERE
  received.Date BETWEEN '2003-09-12 00:00:00' AND '2003-09-12 23:59:59'
    AND
      received.ID IN NOT NULL

Regards,
Matt.

Internet Services

Web Development

Digital Marketing

Consumer Tech