Kenny_Luck 0 Posted November 2 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 Quote Share this post Link to post Share on other sites
Barand 1,389 Posted November 2 You need a space between NOT and EXISTS Quote Share this post Link to post Share on other sites
Kenny_Luck 0 Posted November 2 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) Quote Share this post Link to post Share on other sites
Kenny_Luck 0 Posted November 2 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 Quote Share this post Link to post Share on other sites
Kenny_Luck 0 Posted November 2 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 Quote Share this post Link to post Share on other sites
Barand 1,389 Posted November 2 (edited) 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 by Barand Quote Share this post Link to post Share on other sites
Kenny_Luck 0 Posted November 4 thanks for your help once again Quote Share this post Link to post Share on other sites
Psycho 565 Posted November 4 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" Quote Share this post Link to post Share on other sites
Barand 1,389 Posted November 4 Correct, but I was trying to illustrate where his method was going wrong by showing how his approach should be used. 1 Quote Share this post Link to post Share on other sites