MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE drones ( 
    manufacturer varchar(50)
  , serial int
  , type int
  , purchase_date datetime
);

INSERT INTO drones (manufacturer, serial, type, purchase_date)
SELECT 'Lockheed Martin',1,1,'20200101' UNION ALL
SELECT 'Aerial Robotic Systems',2,10,'20200201' UNION ALL
SELECT 'Amazon',3,100,'20200301' UNION ALL
SELECT 'Northrup Grumman',4,2,'20200401' UNION ALL
SELECT 'DJI',5,20,'20200501' UNION ALL
SELECT 'Titan Aerospace',6,200,'20200601'
;


CREATE TABLE maintenance ( dronetype int, mxtype varchar(50) );

INSERT INTO maintenance (dronetype, mxtype)
SELECT 1,'Replace propeller' UNION ALL
SELECT 1,'Commissioned' UNION ALL
SELECT 100,'Commissioned' UNION ALL
SELECT 2,'Commissioned' UNION ALL
SELECT 20,'Commissioned' UNION ALL
SELECT 200,'DECOMMISSIONED'
;
SELECT * FROM drones ;


manufacturer
serial
type
purchase_date


Lockheed Martin
1
1
2020-01-01 00:00:00

Aerial Robotic Systems
2
10
2020-02-01 00:00:00

Amazon
3
100
2020-03-01 00:00:00

Northrup Grumman
4
2
2020-04-01 00:00:00

DJI
5
20
2020-05-01 00:00:00

Titan Aerospace
6
200
2020-06-01 00:00:00


SELECT * FROM  maintenance ;


dronetype
mxtype


1
Replace propeller

1
Commissioned

100
Commissioned

2
Commissioned

20
Commissioned

200
DECOMMISSIONED


SELECT d.manufacturer, d.serial, d.type, d.purchase_date, m.mxtype
FROM drones d
INNER JOIN maintenance m ON d.type = m.dronetype ;


manufacturer
serial
type
purchase_date
mxtype


Lockheed Martin
1
1
2020-01-01 00:00:00
Replace propeller

Lockheed Martin
1
1
2020-01-01 00:00:00
Commissioned

Amazon
3
100
2020-03-01 00:00:00
Commissioned

Northrup Grumman
4
2
2020-04-01 00:00:00
Commissioned

DJI
5
20
2020-05-01 00:00:00
Commissioned

Titan Aerospace
6
200
2020-06-01 00:00:00
DECOMMISSIONED


SELECT d.manufacturer, d.serial, d.type, d.purchase_date, m.mxtype
FROM drones d
LEFT OUTER JOIN maintenance m ON d.type = m.dronetype ;


manufacturer
serial
type
purchase_date
mxtype


Lockheed Martin
1
1
2020-01-01 00:00:00
Replace propeller

Lockheed Martin
1
1
2020-01-01 00:00:00
Commissioned

Amazon
3
100
2020-03-01 00:00:00
Commissioned

Northrup Grumman
4
2
2020-04-01 00:00:00
Commissioned

DJI
5
20
2020-05-01 00:00:00
Commissioned

Titan Aerospace
6
200
2020-06-01 00:00:00
DECOMMISSIONED

Aerial Robotic Systems
2
10
2020-02-01 00:00:00
null


SELECT d.manufacturer, d.serial, d.type, d.purchase_date, m.mxtype
FROM drones d
LEFT OUTER JOIN maintenance m ON d.type = m.dronetype 
WHERE m.mxtype <> 'commissioned' ;


manufacturer
serial
type
purchase_date
mxtype


Lockheed Martin
1
1
2020-01-01 00:00:00
Replace propeller

Titan Aerospace
6
200
2020-06-01 00:00:00
DECOMMISSIONED


SELECT d.manufacturer, d.serial, d.type, d.purchase_date, m.mxtype
FROM drones d
LEFT OUTER JOIN maintenance m ON d.type = m.dronetype 
WHERE m.mxtype = 'commissioned' ;


manufacturer
serial
type
purchase_date
mxtype


Lockheed Martin
1
1
2020-01-01 00:00:00
Commissioned

Amazon
3
100
2020-03-01 00:00:00
Commissioned

Northrup Grumman
4
2
2020-04-01 00:00:00
Commissioned

DJI
5
20
2020-05-01 00:00:00
Commissioned


SELECT d.manufacturer, d.serial, d.type, d.purchase_date
FROM drones d
WHERE NOT EXISTS (
    SELECT 1 /* This can be anything. It's ignored. */
    FROM maintenance m 
    WHERE m.mxtype = 'commissioned' 
        AND m.dronetype = d.type
    )
;


manufacturer
serial
type
purchase_date


Aerial Robotic Systems
2
10
2020-02-01 00:00:00

Titan Aerospace
6
200
2020-06-01 00:00:00

manufacturer	serial	type	purchase_date
Lockheed Martin	1	1	2020-01-01 00:00:00
Aerial Robotic Systems	2	10	2020-02-01 00:00:00
Amazon	3	100	2020-03-01 00:00:00
Northrup Grumman	4	2	2020-04-01 00:00:00
DJI	5	20	2020-05-01 00:00:00
Titan Aerospace	6	200	2020-06-01 00:00:00

dronetype	mxtype
1	Replace propeller
1	Commissioned
100	Commissioned
2	Commissioned
20	Commissioned
200	DECOMMISSIONED