Sponsored Content
Special Forums UNIX and Linux Applications MYSQL: Help understanding this working query Post 302470809 by rethink on Thursday 11th of November 2010 06:56:18 AM
Old 11-11-2010
MYSQL: Help understanding this working 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 combination of the physical and virtual hosts inserting a "-" into the firmware column as this doesn't apply to the virtual hosts. NOTE: that the status of the physical hosts is stored in the admin table but for virtual hosts is a column within its own table

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 output

Code:
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`');

Im really struggling to understand the use of the A and T within this query ? what is this technique called ? i looked at temporary tables and placeholders but they are different things altogether .Also what it is doing in the line with the UNION ALL . any help understanding this would be great

Cheers
 

8 More Discussions You Might Find Interesting

1. Programming

How to query one to many mysql

Hi there, I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks! YanYan (0 Replies)
Discussion started by: pinkgladiator
0 Replies

2. Shell Programming and Scripting

? parameter in mysql query

I am debugging some code and came across ? in the WHERE cause in a mysql query. Is this possible and what situations would this be used? SELECT ip, count FROM table WHERE domain=? thanks & regards (2 Replies)
Discussion started by: hazno
2 Replies

3. Web Development

Complex MySQL Query(s)

Hi all, I have a bit of an issue, I am working on a bit of a CMDB for a friend, it's to do with real estate. Anyway, this is my situation. I have a table which contains all the properties (forsale, sold, etc) in the DB named "property". Now, this is what I want to achieve, I wish to... (5 Replies)
Discussion started by: STOIE
5 Replies

4. Web Development

mysql query help

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so ? i tried many functions but nothing works please advice what is the query... (6 Replies)
Discussion started by: mogabr
6 Replies

5. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to access mysql query from database , for that i have tried the below code #! /bin/bash TABLE_NAME=database1 USER_NAME=root IP_ADDR=111.20.9.256 somevar=`echo "select altid from alert where altid='2724'"| mysql -h $IP_ADDR -u $USER_NAME $TABLE_NAME ` echo $somevar ... (1 Reply)
Discussion started by: aish11
1 Replies

6. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to run below query on shellscript but having one problm. ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME" "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and Date='$latest Date';" ) here Date column contans different below dates 2011-12-01... (11 Replies)
Discussion started by: aish11
11 Replies

7. Programming

mysql query help

Hello i have created mysql query to compare to values and get difference in percentage as following: SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Discussion started by: mogabr
1 Replies

8. Programming

Need help in mysql query

Hi All, i have a table in mysql with the following data Table name Test Assettype Serial_No Status location Mouse 123456 In Stock chennai Mouse 98765 Allocated chennai Keyboard ... (2 Replies)
Discussion started by: venkitesh
2 Replies
SRU::Server(3pm)					User Contributed Perl Documentation					  SRU::Server(3pm)

NAME
SRU::Server - respond to SRU requests via CGI::Application SYNOPSIS
package MySRU; use base qw( SRU::Server ); sub explain { my $self = shift; # $self->request isa SRU::Request::Explain # $self->response isa SRU::Response::Explain } sub scan { my $self = shift; # $self->request isa SRU::Request::Scan # $self->response isa SRU::Response::Scan # $self->cql is the root node of a CQL::Parser-parsed query } sub searchRetrieve { my $self = shift; # $self->request isa SRU::Request::SearchRetrieve # $self->response isa SRU::Response::SearchRetrieve # $self->cql is the root node of a CQL::Parser-parsed query } package main; MySRU->new->run; DESCRIPTION
This module brings together all of the SRU verbs (explain, scan and searchRetrieve) under a sub-classable object based on CGI::Application. METHODS
explain This method is used to return an explain response. It is the default method. scan This method returns a scan response. searchRetrieve This method returns a searchRetrieve response. CGI
::APPLICATION METHODS setup Sets the "run_modes", "mode_param" and the default runmode (explain). cgiapp_prerun Parses the incoming SRU request and if needed, checks the CQL query. cgiapp_postrun Sets the content type (text/xml) and serializes the response. error_mode Stub error runmode. AUTHORS
o Brian Cassidy <bricas@cpan.org> o Ed Summers <ehs@pobox.com> perl v5.12.4 2009-11-20 SRU::Server(3pm)
All times are GMT -4. The time now is 07:33 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy