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

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:

 SQLite Tutorial for beginners
SQLite Tutorial for beginners
1x1 px

Equipment
NameType
CRQText
DeviceText
ProjectText
NotesText
Device
NameType
DescriptionText Unique
ManufacturerText
ModelText
SiteText
BuildingText
RoomText
NotesText

Equipment.Device = Device.Description

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

DeviceManufacturerModelSite
rsf1-eo1-x129aCisco3560v2RSFC
rsf3-v136aJuniperEX4200RSFC
rsf1-eo1-x129bCisco3560v2RSFC
rsf3-v135aJuniperEX4200RSFC

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.

Displayed pages will look similar to the following:

CRQ000001066121 - Equipment

DeviceManufacturerModelSite
am-asf14-f60aJuniperASFC
am-asf22-f61aJuniperSSG-520MASFC
yu-asf12-x3aCisco3560v2ASFC
yu-asf12-x3bCisco3560v2ASFC
ar-asf2-x135aJuniperEX4200ASFC
ar-asf32-x136aJuniperEX4200ASFC
yu-asf12-x23aCisco3560v2ASFC
yu-asf12-x23bCisco3560ASFC

Equipment not found in Device table

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

Related articles:

  1. Using SQL Inner Join
  2. Using SQLite
  3. Querying an SQLite Database With PHP
  4. DB Browser for SQLite on OS X
  5. PHP for Apache on OS X El Capitan