Sql query cant run

[Kenny_Luck]

SELECT RentID FROM rental WHERE NOTEXISTS(SELECT plateNum FROM rental WHERE Dispatch >= '2019/10/8' AND Dropoff <= '2019/10/5' )

SELECT RentID FROM rental WHERE NOTEXISTS(SELECT plateNum FROM rental WHERE Dispatch >= "2019/10/8" AND Dropoff <= "2019/10/2" ) LIMIT 0, 25

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT plateNum FROM rental WHERE Dispatch >= '2019/10/8' AND Dropoff <= '2019/1' at line 1

[Barand]

You need a space between NOT and EXISTS

[Kenny_Luck]

but if i added space in between Not and Exists , it became
 

3 errors were found during analysis.

Unrecognized keyword. (near "NOT" at position 32)

Unrecognized keyword. (near "EXISTS" at position 36)

Unexpected token. (near "(" at position 42)

[Kenny_Luck]

ok i solved the problem , but now there is another problem which i filtered and displayed the data that i don't one with SELECT * FROM rental WHERE  Dispatch <= '2019/10/08' AND Dropoff >= '2019/10/02' , but now how do i filter the data that i wanted

[Kenny_Luck]

1 hour ago, Kenny_Luck said:

3 errors were found during analysis.

Unrecognized keyword. (near "NOT" at position 32)

Unrecognized keyword. (near "EXISTS" at position 36)

Unexpected token. (near "(" at position 42)

So if i write SELECT RentID FROM rental WHERE NOTEXISTS (SELECT * FROM rental WHERE Dispatch <= "2019/10/8" AND Dropoff >= "2019/10/2" ) i cant made it to display the data i wanted , when i put in to the mysql to check for error it poped out something like this at the above , i need help

[Barand]

1 hour ago, Kenny_Luck said:

So if i write SELECT RentID FROM rental WHERE NOTEXISTS (SELECT * FROM rental WHERE Dispatch <= "2019/10/8" AND Dropoff >= "2019/10/2" )

What happened to the space after the NOT?

Date formats are wrong - should be 2019-10-08 and 2019-10-02

In addition the query logic in wrong, - the EXISTS condition will be applied to every row so if such a record exists then no rows will be output.

Try...

SELECT RentID FROM rental 
WHERE RentID NOT IN (
                    SELECT RentID 
                    FROM rental 
                    WHERE Dispatch <= "2019-10-08" 
                           AND Dropoff >= "2019-10-02" 
                    ) ;                                                 

or

SELECT a.RentID 
FROM rental a
     LEFT JOIN
     (
     SELECT RentID 
     FROM rental 
     WHERE Dispatch <= "2019-10-08" 
           AND Dropoff >= "2019-10-02"
     ) b  USING (RentId)
WHERE b.RentID IS NULL   

 

Edited November 2, 2019 by Barand

[Kenny_Luck]

thanks for your help once again

 

[Psycho]

Maybe I am missing something, but I think there is a simpler solution. The intent appears to be to get a list of IDs that DO NOT match those two conditions. Rather than creating JOINs or Sub-Queries, why not just change the conditions to be a positive check rather than a negative check. So, instead of looking for records that do not match BOTH those conditions, just look for records where either (i.e. OR logical operator) of those conditions are not true by switching the operators from <= to > and the other from >= to <.

This should get the same results in a simpler fashion:

SELECT RentID FROM rental
WHERE Dispatch > "2019-10-08"
   OR Dropoff < "2019-10-02"

 

[Barand]

Correct, but I was trying to illustrate  where his method was going wrong by showing how his approach should be used.