Help understanding this query

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

I have three tables

admin

mac | boxstatus
11:22:33 | ON
AA:BB:CC | OFF

physical_hosts

mac | hostname | firmware
11:22:33 | server1 | 1.34
AA:BB:CC | server2 | 1.45

virtual_hosts

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

output

mac | hostname | firmware | status
11:22:33 | server1 | 1.34 | ON
11:22:33 | VM-1 | - | RUNNING
11:22:33 | VM-3 | - | RUNNING
AA:BB:CC | server2 | 1.45 | OFF
AA:BB:CC | VM-2 | - | CONFIGURED

this is the query that creates the above table

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

a little easier to read/understand now, eh?

so which parts are you having trouble with?

I dont really understand the use of the “a” and “t” . Why use them? why not use the actual table names?

eg. why use a.hostname and not physical_hosts.hostname ?

surely there is another reason other than it is shorter?

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

thanks again for helping me out on this

you have a syntax error in the CONCAT

the string ends with the first single quote at ‘-’ AS -

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)

[B]
mac | hostname | firmware | status[/B]
11:22:33 | server1 | 1.34 | ON
11:22:33 | VM-1 | - | RUNNING
11:22:33 | VM-3 | - | RUNNING
AA:BB:CC | server2 | 1.45 | OFF
AA:BB:CC | VM-2 | - | CONFIGURED

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)

the output should look like this

[B]
mac | hostname | firmware | status[/B]
11:22:33 | server1 | 1.34 | ON
11:22:33 | VM-1 | - | RUNNING
11:22:33 | VM-3 | - | RUNNING

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

Cheers

depends

if all you are looking for is to remove the OFF rows, that’s easy

but what if there is also an ON row? how does the OFF and ON stuff work?

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”

thanks again for helping with this

so as long as there is at least one row with OFF for a physical server, you don’t want that physical server, or any of its virtual servers?

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.

yep thats correct

try this (untested, obviously)…


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

thanks a lot Rudy. you’ve been a great help

ps: I have just ordered your book