First of all, I know the question title may have been asked before but my query is slightly different. I have searched but I couldn't find what I am looking for.IF anyone knows that this question asked before, please provide a link to that post.
So My question is this;
This is a minicab booking website. I have VEHICLES tables. In this table I have 4 vehicles. Saloon, Estate, MPV, Minibus. These vehicles have values like passengers and luggage capacity etc. as follows;
- Saloon => passengers=4 AND luggage_capacity=5
- Estate => passengers=4 AND luggage_capacity=8
- MPV => passengers=6 AND luggage_capacity=10
- Minibus=> passengers=10 AND luggage_capacity=30
Now, When visitor enters information of how many passengers and how many luggages, Sql query should return the correct vehicle for the information given.
Example: Visitor Selects 3 passengers and 5 luggages. This should return SALOON vehicle. If passengers is 6 and no luggage MPV should return as result and so on.
I have tried the following sql query but wrong vehicle is displayed.
SELECT name FROM vehicles WHERE passengers >= $passengers AND luggage_capacity >= $luggage_capacity
I hope I could explain what I meant. Any help is appreciated.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `vehicles` ADD INDEX `vehicles_idx_passengers` (`passengers`);
SELECT vehicles.name FROM vehicles WHERE vehicles.passengers >= $passengers AND vehicles.luggage_capacity >= $luggage_capacity