sql - 如何编写此 MySQL 查询？

Question

我正在使用灯组处理 Assets 数据库。

在此示例中考虑以下 5 个表:

Assets ，服务器，笔记本电脑，桌面，软件

所有表都有一个主键id，它是一个唯一的 Assets id。

每个对象都具有所有 Assets 属性，然后根据 Assets 类型在相应的表中具有附加属性。如果类型是服务器、台式机或笔记本电脑，它在软件表中也有项目。

下面是表创建语句:

// 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 />";

?>

如何查询数据库，以便在按 ID 搜索时收到与该 Assets ID 相关的所有字段？

谢谢。

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>