Jump to content
Kenny_Luck

Sql query cant run

Recommended Posts

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by Barand

Share this post


Link to post
Share on other sites

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"

 

Share this post


Link to post
Share on other sites

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

  • Thanks 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.