MySQL LEFT JOIN with conditional logic for empty values or most recent record

Data and Databases
1

I need help creating a SQL query that displays each equipment record along with either null values when no inspection exists or the latest inspection details when multiple inspections are available for that equipment.

I have two main tables - one for equipment and another for inspections. My query also includes two other LEFT JOINs that work perfectly fine since they’re simple one-to-one relationships.

Here are my table structures:

CRETE TABLE `equipment_inspection` (
    `inspection_id` int(16) NOT NULL,
    `inspection_equipment_id` varchar(16) NOT NULL,
    `inspection_date` date NOT NULL,
    `equipment_available` varchar(16) NOT NULL,
    `maintenance_needed` varchar(16) NOT NULL,
    `inspection_notes` varchar(256) NOT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

CREATE TABLE `equipment` (
    `equipment_id` int(32) NOT NULL,
    `category` varchar(64) NOT NULL,
    `vendor` varchar(16) NOT NULL,
    `equipment_title` varchar(64) NOT NULL,
    `equipment_description` varchar(128) NOT NULL,
    `quantity` int(32) NOT NULL,
    `serial_number` varchar(32) NOT NULL,
    `internal_id` varchar(32) NOT NULL,
    `purchase_date` date NOT NULL,
    `purchase_cost` varchar(32) NOT NULL,
    `receipt_number` varchar(32) NOT NULL,
    `equipment_photo` varchar(256) NOT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

INSERT INTO `equipment_inspection` VALUES
(10, '1', '2023-05-15', '1', '0', 'second check on this guitar - sounds great'),
(11, '1', '2023-04-20', '1', '0', 'initial inspection completed'),
(12, '1', '2023-06-10', '1', '0', 'routine maintenance check'),
(13, '1', '2023-06-10', '1', '1', 'strings need replacement'),
(14, '1', '2023-06-10', '0', '0', 'currently unavailable'),
(15, '3', '2023-05-25', '1', '0', 'checked the second item');

INSERT INTO `equipment` VALUES
(1, '2', '1', 'Electric Guitar', 'six-string electric guitar with amplifier', 1, '445566', '2001', '2023-05-20', '2,500.00', '7890AB123', 'photos/1_Electric_Guitar.jpg'),
(3, '8', '2', 'Microphone', 'professional recording microphone', 1, '778899', '2003', '2023-04-15', '800.50', '7890AB456', '');

This is my current query attempt:

$query="SELECT * FROM equipment 
  LEFT JOIN equipment_category ON equipment.category = equipment_category.category_id 
  LEFT JOIN vendors ON equipment.vendor = vendors.vendor_id 
  LEFT JOIN equipment_inspection ON equipment.equipment_id = equipment_inspection.inspection_equipment_id 
  WHERE
      equipment_inspection.inspection_id = (
          SELECT MAX(inspection_id)
          FROM equipment_inspection
          WHERE equipment.equipment_id = equipment_inspection.inspection_equipment_id
      )
  ORDER BY equipment.category ASC, equipment.equipment_title ASC";

The problem is that my query only returns one row instead of showing all equipment records. When an item has multiple inspections, it should display the most recent one. When an item has no inspections, it should still appear with empty inspection fields. Right now I’m only getting data for the first equipment item, even though there should be two rows in the result.

2

Your WHERE clause is turning that LEFT JOIN into an inner join. Try a correlated subquery in your SELECT instead. Get all equipment first, then use subqueries to grab the latest inspection details. Something like SELECT equipment.*, (SELECT inspection_date FROM equipment_inspection WHERE inspection_equipment_id = equipment.equipment_id ORDER BY inspection_date DESC, inspection_id DESC LIMIT 1) as latest_inspection_date and do the same for other inspection fields. This way you’ll see all equipment whether they’ve been inspected or not, but still pull the most recent inspection data when it exists.

3

your WHERE clause is killing equipment that doesn’t have inspections. use a window function instead - throw ROW_NUMBER() OVER (PARTITION BY inspection_equipment_id ORDER BY inspection_date DESC, inspection_id DESC) = 1 into the join condition or move it to your LEFT JOIN’s ON clause.

4

Interesting issue! What about moving the subquery into the ON clause? Something like LEFT JOIN equipment_inspection ON equipment.equipment_id = equipment_inspection.inspection_equipment_id AND equipment_inspection.inspection_date = (SELECT MAX(inspection_date) FROM equipment_inspection WHERE inspection_equipment_id = equipment.equipment_id) - though I’m curious what happens if multiple inspections share the same date.