How to write sql query correctly?

Question

MySQL

Hello!
There are 2 tables

cars

CREATE TABLE `cars` (
  `regnum` varchar(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `brand` varchar(20) NOT NULL,
  `model` varchar(20) NOT NULL,
  `type` tinyint(1) UNSIGNED NOT NULL,
  `color` varchar(14) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `fuel` tinyint(1) UNSIGNED NOT NULL,
  `year` int(4) UNSIGNED NOT NULL,
  `mileage` int(10) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

rental

CREATE TABLE `rental` (
  `id` int(5) NOT NULL,
  `car` varchar(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `user_id` int(5) UNSIGNED NOT NULL,
  `price` decimal(19,2) NOT NULL,
  `pick_up_location` varchar(20) NOT NULL,
  `drop_out_location` varchar(20) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `remark` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The challenge is to get

1. Free now
   
2. busy
   
3. Reserved ( start_date > CURRENT_DATE())
   

I ate and I even seemed to be able to get reserved

SELECT DISTINCT c.* FROM cars c join rental r on c.regnum = r.car where r.start_date > CURRENT_DATE()

But the rest does not want to work ((
Who knows how to do it?

Answer 1

Free cars:

select   * 
from cars 
where not exists (
    select 1 
    from rental 
    where 
      rental.car = cars.regnum 
      and CURRENT_DATE() between start_date and end_date
  );

Busy cars

select   * 
from cars 
where  exists (
    select 1 
    from rental 
    where 
      rental.car = cars.regnum 
      and CURRENT_DATE() between start_date and end_date
  );

Reserved cars

select   * 
from cars 
where  exists (
    select 1 
    from rental 
    where 
      rental.car = cars.regnum 
      and start_date > CURRENT_DATE()
  );

Answer 2

Volodymyr Palamar , First of all, according to the structure of the normal form, each record must have a unique identifier, which the car number cannot be, like the color of the car, for example.
Secondly, making a link on the varchar field is not a very good solution, the indexes on them are at least seriously larger, and the search is slower. I am silent that the numbers can at least have Russian letters, not counting some other non-Latin characters, and you have the encoding of the ask field. The rest of the fields, according to the mind, should also be displayed in related tables, such as color, brand, etc., but this does not apply to this particular issue, and it depends on the task.