How to write this MySQL query?

Question

I am working on Asset DB using a lamp stack.

In this example, consider the following 5 tables:

asset, server, laptop, desktop, software

All tables have a primary key id, which is a unique identifier for a resource.

Each object has all the attributes of an asset, and then, depending on the type of asset, has additional attributes in the corresponding table. If the type is a server, desktop, or laptop, it also has items in the software table.

Here are the instructions for creating the table:

// connect to mysql server and database "asset_db"
mysql_connect("localhost", "asset_db", "asset_db") or die(mysql_error());
mysql_select_db("asset_db") or die(mysql_error());

// create asset table
mysql_query("CREATE TABLE asset(
id VARCHAR(50) PRIMARY KEY, 
company VARCHAR(50), 
location VARCHAR(50),
purchase_date VARCHAR(50),
purchase_order VARCHAR(50),
value VARCHAR(50),
type VARCHAR(50),
notes VARCHAR(200))")
or die(mysql_error());  
echo "Asset Table Created.</br />";

// create software table
mysql_query("CREATE TABLE software(
id VARCHAR(50) PRIMARY KEY, 
software VARCHAR(50),
license VARCHAR(50))")
or die(mysql_error());  
echo "Software Table Created.</br />";

// create laptop table
mysql_query("CREATE TABLE laptop(
id VARCHAR(50) PRIMARY KEY, 
manufacturer VARCHAR(50),
model VARCHAR(50),
serial_number VARCHAR(50),
esc VARCHAR(50),
user VARCHAR(50),
prev_user VARCHAR(50),
warranty VARCHAR(50))")
or die(mysql_error());  
echo "Laptop Table Created.</br />";

// create desktop table
mysql_query("CREATE TABLE desktop(
id VARCHAR(50) PRIMARY KEY, 
manufacturer VARCHAR(50),
model VARCHAR(50),
serial_number VARCHAR(50),
esc VARCHAR(50),
user VARCHAR(50),
prev_user VARCHAR(50),
warranty VARCHAR(50))")
or die(mysql_error());  
echo "Desktop Table Created.</br />";

// create server table
mysql_query("CREATE TABLE server(
id VARCHAR(50) PRIMARY KEY, 
manufacturer VARCHAR(50), 
model VARCHAR(50),
warranty VARCHAR(50))")
or die(mysql_error());
echo "Server Table Created.</br />";

?>

How can I query the database so that when I search by id, I get all the associated fields with this resource identifier?

Thank.

Answer 1

SELECT asset.id
    ,asset.company
    ,asset.location
    -- ,... Any more columns from asset
    ,software.software
    ,software.license
    ,laptop.model AS laptop_model
    ,desktop.model AS desktop_model
    -- etc.
    ,COALESCE(laptop.model, server.model, desktop.model) AS model -- assumes only one non-NULL
FROM asset
LEFT JOIN software
    ON software.id = asset.id
LEFT JOIN laptop
    ON laptop.id = asset.id
LEFT JOIN desktop
    ON desktop.id = asset.id
LEFT JOIN server
    ON server.id = asset.id
WHERE asset.id = <your_id>