create view and joining 2 tables query

Question

I'm getting myself tied up in knots over this one, so hopefully, someone can help.

I need to create a view table and join 2 tables together, but I only need to display entries with missing data.

So, I have a table that lists drones I own, including the manufacturer, serial number and the date purchased, and I have another table that lists maintenance data, including the type of maintenance each device received. This table also includes similar data to the first table.

I need to pull the device data from the first table and compare that to the maintenance table and display the drones that have not received a particular type of maintenance.

This is what I have tried

CREATE VIEW uncommissioned AS
SELECT DISTINCT 
    drone.manufacturer AS manufacture, 
    drone.serial, 
    drone.type, 
    drone.purchase_date, 
    maintenance.type AS maintenance
FROM drone 
INNER JOIN maintenance ON drone.type = maintenance.type
WHERE maintenance.type = 'NULL';

But this gives an empty table, any suggestions on where I'm going wrong?

Answer 1

Based on your last comment:

Taking it step-by-step and building up what you want to do (we'll combine these sets later) ....

1. You're looking to check out all of your drones. So let's select columns from drones table to see what's there.

   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
   

2. What's in the maintenance table?

   SELECT * FROM maintenance ;
   

   dronetype | mxtype           
   --------: | :----------------
           1 | Replace propeller
           1 | Commissioned     
         100 | Commissioned     
           2 | Commissioned     
          20 | Commissioned     
         200 | DECOMMISSIONED   
   

3. How do these two tables relate together? Let's hook them together with a JOIN on type. MOTE: I made an assumption and changed maintenance.type to be more descriptive of how I think these relations should be.

   3.1. Which JOIN do we need? ( For an illustration: https://www.edureka.co/blog/sql-joins-types). I'll only look at INNER and OUTER JOINs.

   3.1.1 An INNER JOIN will only give us records that match in BOTH tables.

   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   
   

   That's not really what we want.

   3.1.2 An OUTER JOIN will give us records from one of the tables and the rows that match in the other table. Unmatched rows will be 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 ;
   

   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             
   

   That shows us all of the `drones` with a `NULL` record for the non-matched one.
   

4. But we want to show drones that haven't been Commissioned yet in the maintenance table.

   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   
   

   But now we're missing our unmatched record, and we're showing records we don't want (like the Lockheed Martin record, which has 2 maintenance records with one being Commissioned).

5. So let's choose records from our drones table that don't have a Commissioned record in maintenance. We can do that with a NOT EXISTS.

   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
   

   Now we've got the two drones that don't have a record in maintenance for being Commissioned.

   NOTE: In this final query, we used a correlated subquery to link the maintenance table to the drones table, and we included a condition on our query for the maintenance table to only select records with mxtype or commissioned. And the WHERE NOT EXISTS will filter the query of the drones table to exclude any rows that DO have a match in the subquery.

You can play with the query and data here: db<>fiddle

Answer 2

IS NULL is right, and RIGHT JOIN because NULL in right table maintance
Try it: SELECT DISTINCT drone.manufacturer AS manufacture, drone.serial, drone.type, drone.purchase_date, maintenance.type AS maintenance FROM drone RIGHT OUTER JOIN maintenance ON drone.type = maintenance.type WHERE maintenance.type IS NULL;

Answer 3

I think that you want an anti-LEFT JOIN:

CREATE VIEW uncommissioned AS
SELECT DISTINCT 
    d.manufacturer AS manufacture, 
    d.serial, 
    d.type, 
    d.purchase_date, 
    d.type AS maintenance
FROM drone d
LEFT JOIN maintenance m ON d.type = m.type
WHERE m.type IS NULL;

Notes:

• to chek for nullity, you need the IS NULL construct (your code checks for litteral string 'NULL'):

• if you want to display the maintenance type, you need to take that information from the drone table rather than from the maintenance table

You could also phrase this with a NOT EXISTS condition and a correlated subquery:

CREATE VIEW uncommissioned AS
SELECT DISTINCT 
    d.manufacturer AS manufacture, 
    d.serial, 
    d.type, 
    d.purchase_date, 
    d.type AS maintenance
FROM drone d
WHERE NOT EXISTS (SELECT 1 FROM maintenance m WHERE d.type = m.type)