MySQL search across multiple tables

[zero_ZX]

Hi,

So I'm trying to make a car rental project, and I need to show cars of a specific type, available in a specific period.

I'm currently using this query:

SELECT * FROM car, reservations

WHERE car.car_id = reservations.car

AND car.type = 1 AND reservations.reserved_from >= "2011-12-22"

AND reservations.reserved_to <= "2012-12-20";

 

So this returns all the cars of the type 1 reserved in 2011-12-22 - 2012-12-20 period.

So know that I know which cars are unavailable, how would you go ahead and pick out what's left?

[scootstah]

So you want to return the cars that aren't reserved?

[zero_ZX]

Yep, but only once, as one specific car only can be rented out once, so no duplicate entries

[scootstah]

In the database, is the default value for reserved_from none?

 

If so you should just be able to do WHERE reservations.reserved_from = ""

[zero_ZX]

No, the field is not null, and doesn't have a default value.

[scootstah]

So what is it set to when the car is no longer reserved?

[zero_ZX]

It was just a reservation.

There are multiples rows with the same car but different dates.

[scootstah]

Oh, I see. Is the reserved_to/from fields DATE/DATETIME?

 

If yes you can try

WHERE reservations.reserved_to < CURDATE()

[zero_ZX]

The plan is to check in the future, not just today :/

[scootstah]

Hmm. Without knowing how you wish to use the information I can't offer a better suggestion.

 

But if it's available today, it should also be available in the future no?

[zero_ZX]

You put like you want car type 1, and it must be available from date_start to date_end

Then it should show all cars of type 1, not reserved in period date_start and date_end.