Hi there, I have a query that works just fine, but im having a little problem understanding how it works and have been struggling to find any docs that explain the techniques that are being used. If anybody out there could explain how this is put together then that would be great
mac | virtual_name | status
11:22:33 | VM-1 | RUNNING
11:22:33 | VM-3 | RUNNING
AA:BB:CC | VM-2 | CONFIGURED
The following query outputs a new table combining the physical and virtual hosts inserting a “-” into the firmware column as this doesn’t apply to the virtual hosts
SELECT
`A`.`mac` AS `mac`,
`A`.`hostname` AS `hostname`,
`A`.`firmware` AS `firmware`,
`T`.`boxstatus` AS `status` FROM (`MYDB`.`physical_hosts` `A` join `MYDB`.`admin` `T`
ON((`A`.`mac` = `T`.`mac`))) UNION ALL SELECT
`virtual_hosts`.`mac` AS `mac`,
`virtual_hosts`.virtual_name` AS `virtual_name`,
'-' AS `-`
`virtual_hosts`.`status` AS `status`
FROM `MYDB`.`virtual_hosts` ORDER BY `mac`');
first thing we’ll do is both reformat the query and trim the unnecessary cruft…
SELECT a.mac
, a.hostname
, a.firmware
, t.boxstatus AS status
FROM mydb.physical_hosts AS a
INNER
JOIN mydb.admin AS t
ON t.mac = a.mac
UNION ALL
SELECT mac
, virtual_name
, '-'
, status
FROM mydb.virtual_hosts
ORDER
BY mac
nope, not in this example – the shorter table alias names means that it’s easier to scan/read/understand the query
compare –
SELECT a.mac
, a.hostname
, a.firmware
, t.boxstatus AS status
FROM mydb.physical_hosts AS a
INNER
JOIN mydb.admin AS t
ON t.mac = a.mac
SELECT mydb.physical_hosts.mac
, mydb.physical_hosts.hostname
, mydb.physical_hosts.firmware
, mydb.admin.boxstatus AS status
FROM mydb.physical_hosts
INNER
JOIN mydb.admin
ON mydb.admin.mac = mydb.physical_hosts.mac
so using the shorter alias names is like trimming the trees so that you can better understand the forest, but it’s often optional
there are, however, instances where you ~must~ use an alias, where you incorporate the same table into a query more than once…
SELECT hometeam.name AS home_team
, scores.home_score
, awayteam.name AS away_team
, scores.away_score
FROM scores
INNER
JOIN teams AS hometeam
ON hometeam.team_id = scores.home_id
INNER
JOIN teams AS awayteam
ON awayteam.team_id = scores.away_id
thank you r937 (as always, very informative). The reason I am trying to understand how this is put together is because currently that query exists within a view, which works just fine. But I am trying to convert it to a stored procedure which creates a read only memory based table instead of the view. (PS I am passing the target table name as a parameter, but that is not relevant to my query)
# /bin/mysql -uUSR -pPASS MYDB -e "call all_servers('all_servers_table')"
[B][COLOR="Red"]ERROR 1113 (42000) at line 1: A table must have at least 1 column[/COLOR][/B]
here is the stored procedure
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `all_servers`(IN param1 varchar(20))
BEGIN
SET @s = CONCAT('DROP TABLE IF EXISTS ', param1);
PREPARE stm FROM @s;
EXECUTE stm;
SET @s = CONCAT('CREATE TABLE ',param1,' ENGINE=MEMORY[COLOR="Red"] SELECT
`A`.`mac` AS `mac`,
`A`.`hostname` AS `hostname`,
`A`.`firmware` AS `firmware`,
`T`.`boxstatus` AS `status` FROM (`MYDB`.`physical_hosts` `A` join `MYDB`.`admin` `T`
ON((`A`.`mac` = `T`.`mac`))) UNION ALL SELECT
`virtual_hosts`.`mac` AS `mac`,
`virtual_hosts`.virtual_name` AS `virtual_name`,
'-' AS `-`
`virtual_hosts`.`status` AS `status`
FROM `MYDB`.`virtual_hosts` ORDER BY `mac`');[/COLOR]
PREPARE stm FROM @s;
EXECUTE stm;
END$$
Like I say, within a view this is fine, when in a stored procedure it isnt
blast those single quotes always get me. Ive changed it to a double quote around the “-” and all seems fine. Plus thank you again for the help with the table aliases.
One more thing, and id totally understand if you elect not to assist me with this one as im going slightly off on a tangent slightly. I have been asked to adjust the query so that if a physical host is switched to OFF then it will not appear in the output, and neither … and this is the bit where im stuck … will the virtual hosts that are associated with it ?
So the table that previously looked like this (using the table data from the original post)
Will now not have the physical host “server2” because it is off and and and virtual hosts that are associated with this OFF box (in this case the one called VM-2)
Is there an easy way for me to do this within this particular query or would it be easier for me to leave the above query alone and instead run an additional query on top ?. Ideally I would be able to get it all in one. Is this possible?
If anyone could point me in the right direction that would be great
well, the ON | OFF status only applies to the records in the “physical hosts” table. The objects in the “virtual hosts” table have different status’s (e.g. “running” and “configured”) but im not too worried about those. All “virtual hosts” have a parent “physical host”, and there can be multiple virtuals on a physical… basically think of an example of a VMWARE or virtualbox instance called VM-2 that exists on a real ‘physical’ PC called “server2”.
I have been asked to ensure that IF a physical box has been switched to OFF, then make sure we don’t list any of the “virtual hosts” that may be running on it , regardless of what the status of said virtual hosts may be.
If we list everything but those hosts with a status of “OFF”, then we wont be catching the virtual hosts that are on that physical host. its quite conceivable that a connected virtual host will have a status of “running” and the physical host has a status of “OFF”
annoyingly the status of the physical boxes (either ON or OFF) exists in another table called “admin” which complicates things a little , hence the join to the table alias called “t”
If you repeatedly write the longer names (table names), it could be boring to read and debug. So, Just assume: “a” for mydb.physical_hosts and “t” for mydb.admin.
But, writing the initials would be good (until the initials of the table names collide)
For example, “ph” and “a” for physical_hosts and admin.
SELECT phost.mac
, phost.hostname
, phost.firmware
, admin.boxstatus AS status
FROM mydb.physical_hosts AS phost
INNER
JOIN mydb.admin AS admin
ON admin.mac = phost.mac
AND admin.boxstatus = 'ON'
WHERE NOT EXISTS
( SELECT 'uh oh'
FROM mydb.admin
WHERE mac = phost.mac
AND boxstatus = 'OFF' )
UNION ALL
SELECT vhost.mac
, vhost.virtual_name
, '-'
, vhost.status
FROM mydb.physical_hosts AS phost
INNER
JOIN mydb.admin AS admin
ON admin.mac = phost.mac
AND admin.boxstatus = 'ON'
INNER
JOIN mydb.virtual_hosts AS vhost
ON vhost.mac = ph.mac
WHERE NOT EXISTS
( SELECT 'uh oh'
FROM mydb.admin
WHERE mac = phost.mac
AND boxstatus = 'OFF' )
ORDER
BY mac