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