Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Query not extract my expected output

I have a two tables in MySQL database:

  • vehicle
  • vehicle logs

When the user inserts a vehicle into the system, the vehicle logs table also generates a new ‘vehicle added’ row. When something happens to the vehicle, the log table also updates accordingly. Below are the vehicle and vehicle logs tables:

Vehicle Table

CREATE TABLE `asset_vehicle` (
  `id` varchar(50) NOT NULL,
  `asset_code` varchar(50) NOT NULL,
  `engine_capacity` varchar(20) NOT NULL,
  `engine_no` varchar(50) NOT NULL,
  `make` varchar(80) NOT NULL,
  `model` varchar(50) NOT NULL,
  `no_of_doors` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Vehicle Logs Table

CREATE TABLE `asset_vehicle_logs` (
  `id_pk` varchar(50) NOT NULL,
  `description` varchar(2000) NOT NULL,
  `id` varchar(40) NOT NULL,
  `date_log` date DEFAULT NULL,
  `time_log` time DEFAULT NULL,
  PRIMARY KEY (`id_pk`),
  KEY `FK7up0y9054d64e3mvue3r4pe9e` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

‘id’ acts as primary key for the Vehicle Table and as foreign key for the vehicle logs table.

This is an issue. The logs table maintains several statuses for vehicles. I want to filter based on the ‘date_log’ column, to keep recent states only. when one status is selected, the query should return the relevant vehicle. One vehicle should not have different statuses in the same data range.

I wrote a query to perform this. But it returns the same vehicles in other reports.

Query 1

select * from asset_vehicle_logs l join asset_vehicle v on l.id = v.id where l.description = 'Vehicle verified' and  l.date_log between '2023-12-01' and '2023-12-31' group by l.id  order by l.date_log desc,l.time_log desc;

Query 2

select * from asset_vehicle_logs l join asset_vehicle v on l.id = v.id where l.description = 'Vehicle rejected' and  l.date_log between '2023-12-01' and '2023-12-31' group by l.id  order by l.date_log desc,l.time_log desc;

Updates

Vehicle Table sample data
enter image description here

Vehicle logs table sample data filtered based on the ID of the vehicle table.

sample logs table 1(filter by ID)
enter image description here

sample logs table 2(filter by ID)
enter image description here

sample logs table 3(filter by ID)
enter image description here

Tags:

2

Answers


  1. 0

    I’m afraid but we would need a sample of the data set. If you can create a minimal dataset that can be used to reproduce your problem, please do that.

    The queries should return vehicles that has entries in the log table with description = 'Vehicle rejected' and description = 'Vehicle verified'.

    The problem is, in the database structure that you presented there is no mechanism to make sure there is only one entry in the log table per vehicle ID. Are you 100% it is unique?

    What about adding UNIQUE key on 'id' varchar(40) NOT NULL in the asset_vehicle_logs table? That would ensure there’s only one entry in the logs for the single vehicle.

    Login or Signup to reply.
  2. 0

    If the relationship between vehicle and vehicle_logs is one-to-many, nothing prevents a log from being created several times for the same vehicle, on the same day, in the vehicle_logs table. If you want each vehicle to only have one log at a time, you need to invert the relationship and move your foreign key to the vehicle table.

    Also, for clarity, I would rename ‘id’ and ‘id_pk’ in your vehicle_logs table:

    CREATE TABLE `asset_vehicle_logs` ( `id` varchar(50) NOT NULL, `description` varchar(2000) NOT NULL, `id_vehicle` varchar(40) NOT NULL, `date_log` date DEFAULT NULL, `time_log` time DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK7up0y9054d64e3mvue3r4pe9e` (`id_vehicle`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    I hope it helps!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search