I have an SQLite database stored on my MacBook Pro laptop that I use to track work requests. The database file is named CRQ.db, since it tracks work done under a Change Request (CRQ). Within that database are several tables two of which are "Equipment" and "Device". For every CRQ that I need to deal with, I store records with the CRQ number in a "CRQ" column and an identifier for each piece of equipment affected by work done under that CRQ in a "Device" column. information on equipment that is affected by the CRQ in the Equipment table. I have another table named "Device" that holds details for each device, including the manufacturer and model number as well as the physical location of the device. The information in the "Description" column in the "Device" table matches the "Device" name in the "Equipment" table. The two tables have the following structure:

Equipment
Name	Type
CRQ	Text
Device	Text
Project	Text
Notes	Text

Device
Name	Type
Description	Text Unique
Manufacturer	Text
Model	Text
Site	Text
Building	Text
Room	Text
Notes	Text

I use an SQL Inner Join to display a list of matching records from the Device table for each piece of equipment associated with a CRQ in the Equipment table. I list the device name, manufacturer, model number and the site where it is located. E.g.:

CRQ000001115427 - Equipment

Device	Manufacturer	Model	Site
rsf1-eo1-x129a	Cisco	3560v2	RSFC
rsf3-v136a	Juniper	EX4200	RSFC
rsf1-eo1-x129b	Cisco	3560v2	RSFC
rsf3-v135a	Juniper	EX4200	RSFC

There are cases where I've entered a device name in the Equipment table, but I don't have a matching entry in the Device table. In such cases, I also want to list just the device names that occur in the Equipment table which can't be found in the Device table, so that I know I need to put entries in the Device table for those pieces of equipment. To find all of the records that occur in one table that don't occur in another table, you can use an SQL command like the following one:

SELECT name FROM table2 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.name = table2.name)

In this example, I can run sqlite from a command-line interface (CLI), i.e., a Terminal window on the Mac and enter an SQL command like the following one.

$ sqlite3 CRQ.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> SELECT Device FROM Equipment WHERE NOT EXISTS (SELECT * FROM Device WHER
E Equipment.Device = Device.Description) AND CRQ = "CRQ000001066121";
yu-asf22-s31a
yu-asf15-s32a
am-asf15-t101a
am-asf25-t102a
sqlite>

Since I have a PHP script to perform an inner join and display matching records from the two tables on a webpage, I added the following PHP code to it to also display records that don't match between the two tables. I.e., instances where there's a record in one table with no matching record in the other table.

// Find all equipment that isn't listed in the device table
$query_string = "SELECT Device FROM Equipment WHERE NOT EXISTS (SELECT * FROM Device WHERE Equipment.Device = Device.Description) AND CRQ = \"$crq\"";
$results      = $db_handle->query($query_string);
echo "<p>Equipment not found in Device table</p>";
while ($row = $results->fetchArray()) {
    echo $row['Device'] . "
\n";
}

The PHP file, equipment.php, with the entire script to produce the webpage with both matching records and records with no match is as follows:

<!DOCTYPE HTML>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Change Request (CRQ) - Equipment</title>
<style type="text/css">
   table { border: 1px outset black; }
   th { padding: 5px; border: solid 1px black; }
   td { padding: 5px; border: solid 1px black; }
</style>
</head>

<body>

<?php

$filename   = "/Users/jasmith1/Documents/www/CRQ/CRQ.db";

if (isset($_GET["crq"])) {
   $crq = $_GET["crq"];
   $query_string = "SELECT Equipment.Device, Device.Manufacturer, Device.Model, Device.Site from Equipment INNER JOIN Device on Equipment.Device=Device.Description WHERE CRQ=\"$crq\"";
   echo "<h1 style=\"text-align: center\">" . $crq . " - Equipment</h1>";
}
// Display an error message and exit
else {
   exit("Error! No CRQ specified");
}

$db_handle  = new SQLite3($filename);
$results     = $db_handle->query($query_string);

echo "<table>\n";
echo "<tr><th>Device</th><th>Manufacturer</th><th>Model</th><th>Site</th></tr>\n";
while ($row = $results->fetchArray()) {
    echo "<tr><td><a href=\"device.php?device=" . $row['Device'] . 
         "\">" . $row['Device'] .  "</a></td>" .
         "<td>" . $row['Manufacturer'] . "</td>" . 
         "<td>" . $row['Model'] . "</td>" .
         "<td><a href=\"site-equipment.php?site="  . $row['Site'] . "\">" . 
          $row['Site'] . "</a></td></tr>\n";
}
echo "</table>\n\n";

// Find all equipment that isn't listed in the device table 
$query_string = "SELECT Device FROM Equipment WHERE NOT EXISTS (SELECT * FROM Device WHERE Equipment.Device = Device.Description) AND CRQ = \"$crq\"";
$results      = $db_handle->query($query_string);
echo "<p>Equipment not found in Device table</p>";
while ($row = $results->fetchArray()) {
    echo $row['Device'] . "<br>\n";
}

?>

</body>

</html>

I access the information in a browser on the laptop using a URL such as http://localhost/CRQ/equipment.php?crq=CRQ000001066121.

CRQ000001066121 - Equipment

Device	Manufacturer	Model	Site
am-asf14-f60a	Juniper		ASFC
am-asf22-f61a	Juniper	SSG-520M	ASFC
yu-asf12-x3a	Cisco	3560v2	ASFC
yu-asf12-x3b	Cisco	3560v2	ASFC
ar-asf2-x135a	Juniper	EX4200	ASFC
ar-asf32-x136a	Juniper	EX4200	ASFC
yu-asf12-x23a	Cisco	3560v2	ASFC
yu-asf12-x23b	Cisco	3560	ASFC

Equipment not found in Device table

yu-asf22-s31a
yu-asf15-s32a
am-asf15-t101a
am-asf25-t102a

Find entries in one table but not a second table in a database

CRQ000001115427 - Equipment

CRQ000001066121 - Equipment