MySQL Available Cars With multiple owners I have a complex SQL question. We are building a car rental system. We have a car model table, a owner table and a link_model_owner table. In addition our bookings are kept in another table. 

Scheme looks like this: 



Now I need a query to search all available cars of a certain model. I've tried the following Query. However, this query will give multiple results if a linkId is added to the booking database and startDate and endDate are empty there. 

I just want to have a list of unique available linkId's where the car is not rented out (NOW () between startDate and endDate). 

Bear in mind that the booking database will expect to grow with 1000's a day. 

SELECT link_model_owner.id,
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON link_book_user.id = bookings.bookLinkId
INNER JOIN owners ON link_book_user.userId = owners.id
WHERE link_model_owner.isActive = '1'
  AND link_model_owner.modelid = '252'
  AND COALESCE(NOW() NOT BETWEEN bookings.startDate AND bookings.endDate, TRUE);
It wasn't completely obvious what you wanted to use all tables for, especially link_book_user, but I focused on your main question where you wanted to get all cars that were not currently being booked, taking into consideration that startTime and endTime can be null. 

In the query suggestion below I filter bookings on the date already when they're being linked in the join and then the query becomes a bit easier to construct. If a booking has null values as dates the car will be seen as free.

SELECT link_model_owner.id, 
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON (link_model_owner.id = bookings.linkId 
                        AND bookings.startDate &lt;= now() 
                        AND bookings.endDate &gt;= now())
INNER JOIN owners ON owners.id = link_model_owner.ownerId
WHERE bookings.id IS null;


Note: It's important to have indexes on startDate and endDate since your database will grow quite some (as you wrote).