0

o/

So I am working on a rental page for a school project and can't figure out what to do next. :/ So the function's I need to implement are that it should display cars that don't have a reservation yet. As seen below

SELECT * FROM reservering 
            INNER JOIN auto 
                ON auto_id = kenteken 
            WHERE NOT EXISTS (
                SELECT * FROM auto
                WHERE kenteken = auto_id
                )

It doesn't return a value not sure what I did wrong really. And the not displaying a car that has a reservation see code below:

SELECT * FROM reservering 
            INNER JOIN auto 
                ON auto_id = kenteken 
            INNER JOIN merktype 
                ON merktype_id = id
            WHERE auto_id AND ophaaldatum NOT BETWEEN '2022-05-05' AND '2022-05-05' OR retourdatum NOT BETWEEN '2022-05-05' AND '2022-05-07'

Right now it shows the same car multiple times because the same car already has a reservation in the future. I want it to just not display if it already has reservation between the time I am searching for otherwise I will have duplicated reservations on the same day.

Any help will be appreciated take care o>

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
DarkIcey
  • 1

1 Answers1

0

Your main query should be from auto, not reservering. Then you should test whether there's a corresponding row in reservering.

SELECT *
FROM auto AS a
WHERE NOT EXISTS (
    SELECT *
    FROM reservering AS r
    WHERE r.auto_id = a.kenteken
)

or using LEFT JOIN

SELECT a.*
FROM auto AS a
LEFT JOIN reservering AS r ON r.auto_id = a.kenteken
WHERE r.auto_id IS NULL

See Return row only if value doesn't exist

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Will this make the car have multiple reservations and also the auto doesn't have reservering as foreign key so will it still work? – DarkIcey Jul 01 '22 at 21:31
  • This only returns cars that have no reservations. It uses the `auto_id` foreign key in the `reservering` table. You don't need a foreign key the other way. – Barmar Jul 01 '22 at 21:37
  • What do you mean by "make the car have multiple reservations"? `SELECT` doesn't change anything, it's just returning what's already there. If the card doesn't have any reservations, how can there be multiple reservations? – Barmar Jul 01 '22 at 21:39
  • Thank you :D it now shows cars that don't have a reservation yet problem is that it still displays cars even if it has a reservation it shows the future reservations of the same car it doesn't display `SELECT a.*, mt.*, r.* FROM auto AS a LEFT JOIN merktype AS mt ON a.merktype_id = mt.id LEFT JOIN reservering AS r ON r.auto_id = a.kenteken WHERE ophaaldatum NOT BETWEEN '$insertedOphaaldatum' AND '$insertedRetourdatum' AND (r.retourdatum NOT BETWEEN '$insertedOphaaldatum' AND '$insertedRetourdatum') OR (r.auto_id IS NULL)` – DarkIcey Jul 02 '22 at 10:51
  • Please post sample data and the desired result. Post it as plain text so I can copy and paste into the test environment. – Barmar Jul 03 '22 at 20:21