all in one table view

Creating "all in one table" view

In order to see all records in MySQL database, I had to create multiple views and use "right join" to put them together one by one.

First view combines HARDWARE and RACK table, meaning that rack_id column in HARDWARE table is replaced with rackname column from RACK table.
Follow command to create this view which is new virtual table in our database.

CREATE VIEW rack_hardware 
AS 
SELECT 
rack.rackname, 
hardware.serialnumber, 
hardware.cpu, 
hardware.memory, 
hardware.harddisk, 
hardware.nic, 
hardware.hwcomment, 
hardware.office_id, 
hardware.manufacturer_id, 
hardware.vendor_id, 
hardware.team_id, 
hardware.hwstatus_id 
FROM rack 
RIGHT JOIN hardware 
ON rack.rack_id = hardware.rack_id;

Then we use newly created view as new table (virtual one) to combine with next office table.

CREATE VIEW office_hardware 
AS SELECT
office.officename, 
rack_hardware.rackname, 
rack_hardware.serialnumber, 
rack_hardware.cpu, 
rack_hardware.memory, 
rack_hardware.harddisk, 
rack_hardware.nic, 
rack_hardware.hwcomment, 
rack_hardware.manufacturer_id, 
rack_hardware.vendor_id, 
rack_hardware.team_id, 
rack_hardware.hwstatus_id 
FROM office 
RIGHT JOIN rack_hardware 
ON office.office_id = rack_hardware.office_id;

So one by one we replace all "id" columns with real data from respective tables.

CREATE VIEW manufacturer_hardware 
AS 
SELECT 
manufacturer.manufacturername, 
office_hardware.officename, 
office_hardware.rackname, 
office_hardware.serialnumber, 
office_hardware.cpu, 
office_hardware.memory, 
office_hardware.harddisk, 
office_hardware.nic, 
office_hardware.hwcomment, 
office_hardware.vendor_id, 
office_hardware.team_id, 
office_hardware.hwstatus_id 
FROM manufacturer 
RIGHT JOIN office_hardware 
ON manufacturer.manufacturer_id = office_hardware.manufacturer_id;

CREATE VIEW vendor_hardware 
AS 
SELECT 
vendor.vendorname, 
manufacturer_hardware.manufacturername, 
manufacturer_hardware.officename, 
manufacturer_hardware.rackname, 
manufacturer_hardware.serialnumber, 
manufacturer_hardware.cpu, 
manufacturer_hardware.memory, 
manufacturer_hardware.harddisk, 
manufacturer_hardware.nic, 
manufacturer_hardware.hwcomment, 
manufacturer_hardware.team_id, 
manufacturer_hardware.hwstatus_id 
FROM vendor 
RIGHT JOIN manufacturer_hardware 
ON vendor.vendor_id = manufacturer_hardware.vendor_id;

CREATE VIEW team_hardware 
AS 
SELECT 
team.teamname, 
vendor_hardware.vendorname, 
vendor_hardware.manufacturername, 
vendor_hardware.officename, 
vendor_hardware.rackname, 
vendor_hardware.serialnumber, 
vendor_hardware.cpu, 
vendor_hardware.memory, 
vendor_hardware.harddisk, 
vendor_hardware.nic, 
vendor_hardware.hwcomment, 
vendor_hardware.hwstatus_id 
FROM team 
RIGHT JOIN vendor_hardware 
ON team.team_id = vendor_hardware.team_id;

And at the end we have replaced all foreign keys with data from respective tables. Next would be probably rearanging columns in this final table.

CREATE VIEW all_in_one_table 
AS 
SELECT 
hwstatus.status, 
team_hardware.teamname, 
team_hardware.vendorname, 
team_hardware.manufacturername, 
team_hardware.officename, 
team_hardware.rackname, 
team_hardware.serialnumber, 
team_hardware.cpu, 
team_hardware.memory, 
team_hardware.harddisk, 
team_hardware.nic, 
team_hardware.hwcomment 
FROM hwstatus 
RIGHT JOIN team_hardware 
ON hwstatus.hwstatus_id = team_hardware.hwstatus_id;

HOME