How do I write this MySQL query?
- by CT
I am working on an 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 of id, which is a unique asset id.
Every object has all asset attributes and then depending on 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 table create statements:
// 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 do I query the database so that if I search by id, I receive all related fields to that asset id?
Thank you.
