Article: K1000 Reports - Advanced MySQL Query Techniques - Parsing Multiple Substrings from a Field Using Temporary Tables

K1000 Reports - Advanced MySQL Query Techniques - Parsing Multiple Substrings from a Field Using Temporary Tables
__________________________________________________________________________________
__________________________________________________________________________________

(0) Preface

Section I - Definition of Problem
(01) Overview
(02) Original Query Issues
(03) User Defined Functions (UDF) and the K1000

Section II - General Concepts
(04) Order of Execution
(05) Inline Views
(06) Subqueries
(07) User Defined Variables
(08) Cartesian Products

Section III - Data Manipulation Functions
(09) UNION ALL
(10) DISTINCT
(11) GROUP_CONCAT

Section IV - String Manipulation Functions
(12) LENGTH
(13) SUBSTRING
(14) SUBSTRING_INDEX
(15) TRIM

Section V - Subquery Analysis and Variations
(16) Cartesian_Product Subquery Analysis and Variations
(17) Temp_Table_to_Populate_Substring_Index Subquery Analysis and Variations
(18) Filter Subquery Analysis and Variations

Section VI - Full Query Analysis and Variations
(19) Original Query
(20) Original Query Variations
(21) Fully-Operational Query and Analysis
(22) Fully-Operational Query Variations

(X) Conclusion
__________________________________________________________________________________
__________________________________________________________________________________

(0) Preface
__________________________________________________________________________________

Since it logically followed the others, the Local Printers (USB, LPT) report in this blog was at the end:

K1000 Reports - Default, All & Local Printers (w-vbscripts)
http://www.itninja.com/blog/view/k1000-reports-default-all-local-printers-w-vbscripts

However, it was a bit of a buried gem for several reasons, one being string manipulation tricks that I haven't seen done in other K1000 reports. But the main one was the use of subqueries (specifically, inline views) that combined user variable and cartesian product statements to act as a quasi-stored procedure, something that is not possible on the K1000 without an admin login (which customers typically don't have). That combo is not something I came up with myself (lwadwell from Experts Exchange gets full credit for that) and it took me a while to completely understand it - so please don't take this as a glowing review of my own $k1llz... >_< Anyways, I can think of several potential uses for this technique (workaround, fudge... *Holy Grail for implementing stored procedures on the K1000* - hey, I can dream, right?), so I'm sure there are more and wanted to break this down so others can figure out what else can be done with it.
__________________________________________________________________________________

First off, for anyone who is still pretty green to MySQL queries, it probably wouldn't be a bad idea to skim through this blog first to make sure you're comfortable with the basic ideas before getting in too deep in this one:

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
__________________________________________________________________________________
__________________________________________________________________________________

Section I - Definition of Problem
__________________________________________________________________________________
__________________________________________________________________________________

(01) Overview
__________________________________________________________________________________

The Good News

1) My Custom Inventory rules paired with vbscripts did a great job of capturing printers for each user profile on each machine:

Inventorying & Reporting on User Profile-Specific & Other Non-Inventoried Data
http://www.itninja.com/blog/view/inventorying-reporting-on-user-profile-specific-other-non-inventoried-data

2) The output was nice in the Inventory > Computers screen and I even found a workaround for the line break character bug in the Reports:

Workaround for Line Break Character Bug in Reports Generated on Inventory Custom Fields
http://www.itninja.com/blog/view/workaround-for-line-break-character-bug-in-reports-generated-on-inventory-custom-fields
__________________

The Challenge

Figure out a way to pull a list of all of the local printers for each machine from the Custom Inventory fields. First, I had to update my vbscripts to pull the port names so local printers could be identified (and hey, while I was at it, some other useful info). After getting it halfway there, Expert Exchange's billprew once again took it the rest of the way (thank goodness for gurus, huh?). That much accomplished, it was just a matter of parsing the resulting data...
__________________

Monster Strings

To illustrate a bit, here's the strings from one machine's Default Printer and All Printers fields - this is from a (typical) shared PC with (typical) multiple user profiles and (typical) multiple printers. As you might have read in my printer reports blog...

K1000 Reports - Default, All & Local Printers (w-vbscripts)
http://www.itninja.com/blog/view/k1000-reports-default-all-local-printers-w-vbscripts

...I initially took the "easy" way out and decided to target only the first instance of a local printer in the Default Printer field. One look at the raw field data for each, and it's probably easy enough to understand why...
__________________

Default Printer:

8/22/2012 12:04:50 AM - Logged in user: tcasey ------------------------------------------------------ HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * 8/20/2012 8:04:17 PM - Logged in user: janderson ------------------------------------------------------ \\ec01\LANIER LD325 * IP_172.16.1.156 * LANIER MP 2510/LD325 PCL 6 * East Canton 3/26/2012 4:03:12 AM - Logged in user: jryan ------------------------------------------------------ HP LaserJet 6P 7/13/2012 12:05:03 AM - Logged in user: rthompson ------------------------------------------------------ \\ec01\LANIER LD325 8/18/2012 8:05:04 PM - Logged in user: twallace ------------------------------------------------------ \\ec01\ctn150 * IP_172.16.1.150 * HP LaserJet 2300 Series PCL 6 * East Canton 8/22/2012 12:03:38 PM - Logged in user: jcray ------------------------------------------------------ \\ec01\HP LaserJet 2100 Series PCL 6 (Copy 1) * IP_172.16.1.154 * HP LaserJet 2100 Series PCL 6 * East Canton 
__________________

All Printers:

8/22/2012 12:04:08 PM - Logged in user: jcray ------------------------------------------------------ Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * HP LaserJet 6P * LPT1: * HP LaserJet 6P * HP LaserJet 2100 PCL6 * LPT1: * HP LaserJet 2100 PCL6 * hp deskjet 960c * LPT1: * hp deskjet 960c * ctn154 * \\ec01\ctn154 * HP LaserJet P2050 Series PCL6 * cnt_maint_1320 * \\ec01\cnt_maint_1320 * hp LaserJet 1320 PCL 6 * \\ec01\cnt_maint_1320 * IP_172.16.1.157 * hp LaserJet 1320 PCL 6 * Maintenance office \\ec01\HP LaserJet 2100 Series PCL 6 (Copy 1) * IP_172.16.1.154 * HP LaserJet 2100 Series PCL 6 * East Canton \\ec01\LANIER LD325 * IP_172.16.1.156 * LANIER MP 2510/LD325 PCL 6 * East Canton 8/22/2012 12:05:12 AM - Logged in user: tcasey ------------------------------------------------------ Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * HP LaserJet 6P * LPT1: * HP LaserJet 6P * HP LaserJet 2100 PCL6 * LPT1: * HP LaserJet 2100 PCL6 * hp deskjet 960c * LPT1: * hp deskjet 960c * ctn154 * \\ec01\ctn154 * HP LaserJet P2050 Series PCL6 * cnt_maint_1320 * \\ec01\cnt_maint_1320 * hp LaserJet 1320 PCL 6 * \\ec01\cnt_maint_1320 * IP_172.16.1.157 * hp LaserJet 1320 PCL 6 * Maintenance office \\ec01\ctn150 * IP_172.16.1.150 * HP LaserJet 2300 Series PCL 6 * East Canton \\ec01\ec156 * IP_172.16.1.156 * Xerox WorkCentre 5225 * 8/20/2012 8:04:39 PM - Logged in user: janderson ------------------------------------------------------ Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * HP LaserJet 6P * LPT1: * HP LaserJet 6P * HP LaserJet 2100 PCL6 * LPT1: * HP LaserJet 2100 PCL6 * hp deskjet 960c * LPT1: * hp deskjet 960c * ctn154 * \\ec01\ctn154 * HP LaserJet P2050 Series PCL6 * cnt_maint_1320 * \\ec01\cnt_maint_1320 * hp LaserJet 1320 PCL 6 * \\ec01\LANIER LD325 * IP_172.16.1.156 * LANIER MP 2510/LD325 PCL 6 * East Canton 3/26/2012 12:04:13 AM - Logged in user: jryan ------------------------------------------------------ Microsoft Office Document Image Writer HP LaserJet P2015 Series PCL 6 HP LaserJet 6P HP LaserJet 2100 PCL6 hp deskjet 960c ctn154 cnt_maint_1320 \\ec01\LANIER LD325 7/13/2012 12:04:33 AM - Logged in user: rthompson ------------------------------------------------------ Microsoft Office Document Image Writer HP LaserJet P2015 Series PCL 6 HP LaserJet 6P HP LaserJet 2100 PCL6 hp deskjet 960c ctn154 cnt_maint_1320 \\ec01\cnt_maint_1320 \\ec01\HP LaserJet 2100 Series PCL 6 (Copy 1) \\ec01\LANIER LD325 8/18/2012 8:04:21 PM - Logged in user: twallace ------------------------------------------------------ Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * HP LaserJet 6P * LPT1: * HP LaserJet 6P * HP LaserJet 2100 PCL6 * LPT1: * HP LaserJet 2100 PCL6 * hp deskjet 960c * LPT1: * hp deskjet 960c * ctn154 * \\ec01\ctn154 * HP LaserJet P2050 Series PCL6 * cnt_maint_1320 * \\ec01\cnt_maint_1320 * hp LaserJet 1320 PCL 6 * \\ec01\ctn150 * IP_172.16.1.150 * HP LaserJet 2300 Series PCL 6 * East Canton \\ec01\ec156 * IP_172.16.1.156 * Xerox WorkCentre 5225 * 
__________________________________________________________________________________
__________________________________________________________________________________

(02) Original Query Issues
__________________________________________________________________________________

Although I could limit the query to the Default Printers custom inventory field (MCI.SOFTWARE_ID = 6913 on my K1000), the biggest problems I ran into were the lack of unique delimiting characters surrounding the printer names/ports and the massive amount of repetition in the All Printers field - about the only unique data were the usernames. Reading through MySQL's list of string functions, I found one good candidate (SUBSTRING_INDEX) that I could use to:

1) Target an instance of a delimiter directly behind the printer name
2) Remove everything behind it
3) Use a second instance to target a delimiter right before the printer name
4) Remove everything in front of it

I could even use a REGEX statement to only select machines with local printer ports (thanks to adding this to the vbscript's output).

Working under the assumption that "if a machine had a local printer, it would be the default", I did get a number of local printers. However, after browsing through some of the All Printers fields, I had the sinking feeling that I was missing a *lot* of local printers - particularly because a number of machines had multiple local printers. This was especially true of roaming machines that worked out of multiple offices (sales office, home office, branch site office, etc).
__________________

So here's my original query. I'll analyze the more complex main query completely in Section VI (Part 21), but since I just mentioned them here are:

1) The restriction of the query to the Default Printers custom inventory field (MCI.SOFTWARE_ID = 6913).
2) The SUBSTRING_INDEX statement used to pluck the (first) local printer name out of the string.
3) The REGEX statement (MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt') used to limit the query to machines with local printers.

SELECT DISTINCT M.NAME AS 'Machine', M.USER as 'User',
SUBSTRING(L.NAME, 1, length(L.NAME) - 10) as 'Location',
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, '*', 1), " ", -1)
FROM MACHINE_CUSTOM_INVENTORY MCI
WHERE MCI.ID=M.ID and MCI.SOFTWARE_ID = 6913) as 'Local Printer'
FROM MACHINE M
JOIN MACHINE_CUSTOM_INVENTORY MCI on (MCI.ID = M.ID and MCI.SOFTWARE_ID = 6913)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE
MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt'
AND L.NAME rlike 'computers'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
ORDER BY Location, Machine
__________________

As I originally blogged (before massively editing it), it worked very nicely and the output was good, but not 100% accurate (more like ~60%). Aside from depending on the local printer to be the default printer, it also had to be in the first user profile listed in the vbscript output file. So, if another user profile used a network printer as their default, I had to edit the output text file on the machine and re-inventory the machine to capture it so the report would be somewhat more accurate (i.e. not list network printers). It also only captured one local printer, so if there were multiple... and that train of thought made my stomach turn.
__________________________________________________________________________________
__________________________________________________________________________________

(03) Stored Procedures, User Defined Functions (UDF) and the K1000
__________________________________________________________________________________

Running into the limits of the MySQL string functions, I researched and read quite a bit on MySQL stored procedures and UDFs (user defined functions). Unfortunately, in order to create a procedure/function, you must have the INSERT privilege for the MySQL database, which typical K1000 users do not (as far as I can tell... if I'm wrong on this, *PLEASE* let me know!). Just in case I am wrong, here's one I was trying to use...

DROP FUNCTION IF EXISTS STRSPLIT;
DELIMITER $$
CREATE FUNCTION STRSPLIT($Str VARCHAR(20000), $delim VARCHAR(12),$enddelim VARCHAR(12),$ignoredstr VARCHAR(200))
 RETURNS VARCHAR(20000)
BEGIN
 DECLARE suboutput VARCHAR(20000);
 DECLARE output VARCHAR(20000);
 DECLARE countsubstr INT DEFAULT 0;
 DECLARE i INT DEFAULT 0;
 DECLARE ignorestrexist INT DEFAULT 0;
 SET countsubstr = CAST((LENGTH($Str) - LENGTH(REPLACE($Str, $delim, ""))) / LENGTH($delim) AS UNSIGNED) ;
 SET output='';
 SET i = 1;
 myloop: WHILE i <= countsubstr DO
 SET suboutput = SUBSTRING_INDEX(SUBSTRING_INDEX($Str, $delim, i), $enddelim, -1);
 IF $ignoredstr<>'' THEN
 SET ignorestrexist=INSTR(UCASE(suboutput),UCASE($ignoredstr));
 END IF;
 IF ignorestrexist= 0 THEN
 IF output<>''
 THEN SET output = concat(output,',',suboutput);
 ELSE
 SET output = suboutput;
 END IF;
 END IF;
 SET i = i + 1;
 END WHILE myloop;
 IF output = ''
 THEN SET output = null;
 END IF;
 RETURN output;
END $$

SELECT strsplit(s,"* USB"," ","fax")
FROM
(SELECT '8/13/2012 9:04:09 AM - Logged in user: bcarter ------------------------------------------------------ Santa Fe Lanier LD145 * IP_192.168.4.253 * LANIER LD145 PCL 6 * Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP Officejet 6500 E710n-z * USB002 * HP Officejet 6500 E710n-z * HP Deskjet 6940 series * USB001 * HP Deskjet 6940 series * Fax - HP Officejet 6500 E710n-z * USB003 * Fax - HP Officejet 6500 E710n-z * Fax * SHRFAX: * Microsoft Shared Fax Driver * Adobe PDF * My Documents\*.pdf * Adobe PDF Converter * My Documents ' AS example)
InlineView

...only to get the following error when trying to run it and the subsequent query:

Error 1305 - FUNCTION ORG1 strsplit does not exist
__________________

Playing further with a plain INSERT function confirmed the lack of permissions:

Error 1142 - INSERT command denied to user 'R1'@'192.168.11.52' for table 'ASSET'
__________________

Fortunately, Experts Exchange's SQL guru lwadwell had a workaround for this, as I'll try to explain thoroughly. But first, I'd like to cover some concepts - so that hopefully you'll not only understand the main query as a whole, but also be able to make use of the different techniques yourself.
__________________________________________________________________________________
__________________________________________________________________________________

Section II - General Concepts
__________________________________________________________________________________
__________________________________________________________________________________

(04) Order of Execution
__________________________________________________________________________________

A very important idea to keep in mind while building queries is that certain statements will be processed before others (by the query optimizer). Typically, the flow is like this:

1) FROM & JOIN statements (define source)
2) WHERE statements (filter out rows)
3) GROUP BY statements (arrange rows)
4) HAVING statements (filter by groups)
5) SELECT statements (process)
6) ORDER BY statements (sort rows)

A thorough and illustrated example of this process can be found here:

http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
__________________

So, combine this with the order of execution for inline views (as discussed below), and my examples and explanations, and hopefully the flow of the main query (and your own) will make more sense.
__________________________________________________________________________________
__________________________________________________________________________________

(05) Subqueries
__________________________________________________________________________________

Nested SELECT statements (i.e. SELECT ... (SELECT ...)) are called "subqueries" and there are a couple of kinds.
__________________

1) Correlated Subquery

These contain a reference to one or more columns in the outer query, so they are dependent on that query. I have one of these in my original query, just look at the nested SELECT statement that starts at line 3.
__________________

2) Noncorrelated Subquery

This kind can be run independently of the outer query and will return a result set, as it is not dependent on the outer query. All of the subqueries in my fully-operational query are this type, which is *extremely* useful as you will see. I have them indented for clarity.
__________________

More on the differences and some very good examples can be found here:

http://www.devarticles.com/c/a/SQL/Focusing-SQL-Queries/
__________________________________________________________________________________
__________________________________________________________________________________

(06) Inline Views
__________________________________________________________________________________

Another important concept is a variety of noncorrelated subquery called the "inline view". It is constructed similarly to aliasing columns (M.NAME as 'Computer') and allows the outer query to access the columns listed in the subquery. Inline views are an extremely useful tool for several reasons:

1) They force a subquery to be evaluated first (and in order of depth in the case of multiple, nested inline views). Without inline views, a query is at the mercy of the query optimizer to create a query plan (order of statement evaluation and execution). And if the query is not processed as intended, things can take much longer (or hang completely).

2) They behave like unindexed tables (unordered collections of rows) that can be used/referenced in the rest of the query.

3) They can be used to simplify complex queries by removing JOIN operations and condensing several separate queries into a single query.

4) Implied by point 3), but I'll make a more specific point - they allow the creation of temporary tables, including temp tables seeded with data not present in the database. This is how I built a number of example queries included here, which you can run on your own K1000 (via Reports or the MySQL Query Browser).
__________________

Query with Inline View Aliases:

SELECT View2.D as Main1, View2.E as Main2, View2.F as Main3
FROM
(SELECT View1.A as D, View1.B as E, View1.C as F
FROM
 (SELECT '1' as A, '2' as B, '3' as C)
View1)
View2
__________________

Output:

Main1 Main2 Main3
1 2 3
__________________

Analysis:

1) Alias1 subquery (SELECT A, B, C) executed
2) Results from Alias1 subquery grabbed by Alias2 subquery (SELECT View1.A as D, etc)
3) Alias2 subquery executed
4) Results from Alias2 subquery grabbed by main SELECT query (SELECT View2.D as Main1, etc)
5) Main SELECT query executed and results returned
__________________

A couple of points about using inline views:

1) Every inline view needs an alias, even if it is never used. This is so the columns in that subquery can be referenced.

2) Referencing inline view aliases is optional *if* column names in the inline view are unique. If they aren't unique, you can alias the column to something else and just use that in the upper query. I ran into this once when an inline view had M.NAME and L.NAME columns and I couldn't use VIEW.NAME in the upper query, so I aliased L.NAME as 'Location' and used VIEW.Location in the upper query.
__________________

Just to illustrate the part about inline view aliases being optional (in the right conditions), the output for this query would be the same as the previous. Aliasing the main query columns verifies that it is indeed pulling from the View1 inline view.
__________________

Query *without* Inline View Aliases:

SELECT A as Main1, B as Main2, C as Main3
FROM
(SELECT A, B, C
FROM
 (SELECT '1' as A, '2' as B, '3' as C)
View1)
View2
__________________

Output:

Main1 Main2 Main3
1 2 3
__________________________________________________________________________________
__________________________________________________________________________________

(07) User Defined Variables
__________________________________________________________________________________

First, I would like to highlight a very good article that covers the "messy" user variable aspects this query:

http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

This took me a bit to completely get my head around, but running the subquery variations I listed below helped me connect the dots. It also helped that I wrote the original string manipulation parts of the query and therefore had a decent understanding of what I was trying to do. That being the case, I'll try to explain and describe things probably a little too thoroughly just to make sure it all makes sense, but the blog I just referenced is an excellent read on the subject.

Also, I break down the subqueries that use user functions pretty thoroughly in part (17), so consider this a light introduction in which I only cover aspects that are used in the main query. For further rules, restrictions, etc please see the reference manual page:

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
__________________

Syntax:

SELECT @variable:=expression
__________________

Explanation:

As the name would suggest, user defined variables allow the introduction of typical programming logic into statements using variables that you define yourself - in other words, you aren't restricted to what's in your database, although you can also reference this data as well. Combine this with conditional functions like IF and they become a very power tool.

MySQL will let you assign and read a variable at the same time, just be aware that it's not recommended to do this as the evaluation order may differ from what is expected (but isn't that what playing around with constructions is for?). Typical procedure is to use a SET statement to declare the variable, but it can also be declared in a nested SELECT statement constructed as an inline view (as is done in the main query).
__________________

Query:

SELECT @a*@b-@a as 'Product'
FROM
(SELECT @a:=2, @b:=3)
InlineView
__________________

Output:

Product (column name)
4
__________________
__________________

Main query application:

Since I break this down pretty thoroughly in part (17), I thought I'd give an example loosely based on the main query's usage. The IF statement says "if the expression is true, increment the user variable count; if not, then decrement". The user defined variables are initialized in the first inline view (Define_Variables), with the second inline view providing the data used to evaluate the IF statement in the main SELECT statement. As each line of the Counter_Table SELECT statements is processed by the conditional statements in the main query, the counts for @n and @o are increased and/or decreased accordingly. Please also note that the inline views are joined, as in the main query.
__________________

Query:

SELECT @n:=IF(Answer='yes', @n+1, @n-1) as 'Yes Operation',
 @o:=IF(Answer='no', @o+1, @o-1) as 'No Operation'
FROM
(SELECT @n:=0, @o:=0)
Define_Variables,
(SELECT 'yes' as Answer UNION ALL
 SELECT 'yes' UNION ALL
 SELECT 'no' UNION ALL
 SELECT 'yes' UNION ALL
 SELECT 'no' UNION ALL
 SELECT 'no' UNION ALL
 SELECT 'no')
Counter_Table
__________________

Output:

Yes Operations No Operations
1 -1
2 -2
1 -1
2 -2
1 -1
0 0
-1 1
__________________________________________________________________________________
__________________________________________________________________________________

(08) Cartesian Products
__________________________________________________________________________________

Explanation:

The basic idea behind a cartesian product is to exhaustively combine all elements - in other words, making every possible combination among a set of objects. This is the same idea as a basic JOIN (as I briefly discussed in the primer) and as such is typically something to be avoided due to the potential for runaway queries (just imagine five columns with 1,000+ rows each, making *every* possible combination...). However, there *are* circumstances where a very focused cartesian product can be particularly useful - as when combined with inline views and user defined variables to create a decent-sized temporary counter table with relatively little code.

For example, compare the length of these two queries (both of whose goal is to create a Variable column and a Counter column) and you'll see the only difference is that the first produces a "dummy" column (necessary for the IF statement to populate the Counter column). The first works by creating a 3 * 3 * 3 cartesian product (three variables in each inline view factored by three inline views). By changing the number of variables in the inline view (or conversely, the number of inline view statements), the size can be scaled as desired. If this isn't completely clear, just read through section (16) and it will be.

Also a note - I've condensed the first query's spacing so you can make more of an "apples-to-apples" comparison to the second query in regards to size/length. Regardless, scale this to 100+ rows and it should be obvious which is more economical.

Finally, remember - they both accomplish the same thing, it just depends on circumstances as to which you'd want to use.
__________________

Query 1 (using cartesian product):

SELECT Type, @c:=IF(Type=@v, @c+1, 1) as Counter, @v:=Type as Dummy
FROM (SELECT A.Type
FROM (SELECT 'Type1' as Type UNION ALL
SELECT 'Type1' UNION ALL SELECT 'Type1')A,
(SELECT 'Type1' as Type UNION ALL
SELECT 'Type1' UNION ALL SELECT 'Type1')B,
(SELECT 'Type1' as Type UNION ALL
SELECT 'Type1' UNION ALL SELECT 'Type1')C
ORDER BY A.Type) Cartesian_Product,
(SELECT @c:=0,@v:='')Define_Variables
__________________

Output:

Type Counter Dummy
Type1 1 Type1
Type1 2 Type1
Type1 3 Type1
Type1 4 Type1
Type1 5 Type1
Type1 6 Type1
Type1 7 Type1
Type1 8 Type1
Type1 9 Type1
Type1 10 Type1
Type1 11 Type1
Type1 12 Type1
Type1 13 Type1
Type1 14 Type1
Type1 15 Type1
Type1 16 Type1
Type1 17 Type1
Type1 18 Type1
Type1 19 Type1
Type1 20 Type1
Type1 21 Type1
Type1 22 Type1
Type1 23 Type1
Type1 24 Type1
Type1 25 Type1
Type1 26 Type1
Type1 27 Type1
__________________

Query 1 (no cartesian product):

SELECT 1 as Counter, 'Type1' as Port_Type UNION ALL
SELECT 2 as Counter, 'Type1' UNION ALL
SELECT 3 as Counter, 'Type1' UNION ALL
SELECT 4 as Counter, 'Type1' UNION ALL
SELECT 5 as Counter, 'Type1' UNION ALL
SELECT 6 as Counter, 'Type1' UNION ALL
SELECT 7 as Counter, 'Type1' UNION ALL
SELECT 8 as Counter, 'Type1' UNION ALL
SELECT 9 as Counter, 'Type1' UNION ALL
SELECT 10 as Counter, 'Type1' UNION ALL
SELECT 11 as Counter, 'Type1' UNION ALL
SELECT 12 as Counter, 'Type1' UNION ALL
SELECT 13 as Counter, 'Type1' UNION ALL
SELECT 14 as Counter, 'Type1' UNION ALL
SELECT 15 as Counter, 'Type1' UNION ALL
SELECT 16 as Counter, 'Type1' UNION ALL
SELECT 17 as Counter, 'Type1' UNION ALL
SELECT 18 as Counter, 'Type1' UNION ALL
SELECT 19 as Counter, 'Type1' UNION ALL
SELECT 20 as Counter, 'Type1' UNION ALL
SELECT 21 as Counter, 'Type1' UNION ALL
SELECT 22 as Counter, 'Type1' UNION ALL
SELECT 23 as Counter, 'Type1' UNION ALL
SELECT 24 as Counter, 'Type1' UNION ALL
SELECT 25 as Counter, 'Type1' UNION ALL
SELECT 26 as Counter, 'Type1' UNION ALL
SELECT 27 as Counter, 'Type1'
__________________

Output:

Type Counter
Type1 1
Type1 2
Type1 3
Type1 4
Type1 5
Type1 6
Type1 7
Type1 8
Type1 9
Type1 10
Type1 11
Type1 12
Type1 13
Type1 14
Type1 15
Type1 16
Type1 17
Type1 18
Type1 19
Type1 20
Type1 21
Type1 22
Type1 23
Type1 24
Type1 25
Type1 26
Type1 27
__________________________________________________________________________________
__________________________________________________________________________________

Section III - Data Manipulation Functions
__________________________________________________________________________________
__________________________________________________________________________________

Since I'll be illustrating the functions in the "fully-operational" version of my query, I posted it here to save scrolling around so much. Just a note, the indents and line breaks are my own personal preference - I just find the inline views easier to follow like this.
__________________

Query:

SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
GROUP_CONCAT(TRIM(LEADING '* ' FROM Port) SEPARATOR ", ") as 'Ports',
GROUP_CONCAT(Printer SEPARATOR ", ") as 'Local Printers'
FROM
(SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')
Filter
JOIN MACHINE M on (M.ID = Filter.ID)
JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE Filter.Printer <> ''
AND UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|univeral|copy 2|copy 3'
AND L.NAME RLIKE 'computers'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
GROUP BY M.NAME
ORDER by Location, Machine
__________________________________________________________________________________

(09) UNION ALL
__________________________________________________________________________________

Since I'll be using this *very* frequently in my example queries, I thought I'd introduce this before getting into too many examples.

For the full reference manual write-up on this, please see:

http://dev.mysql.com/doc/refman/5.0/en/union.html
__________________

Syntax:

SELECT ... UNION ALL
SELECT
__________________

Explanation:

UNION ALL combines the results from multiple SELECT statements into a single result set (i.e. table with a row for each SELECT statement), uses the column names from the first SELECT statement for the result, and does not remove duplicate rows.

A tip - UNION ALL runs significantly faster than UNION (DISTINCT), although duplicate results must be handled accordingly. See this excellent analysis for more info:

http://www.mysqlperformanceblog.com/2007/10/05/union-vs-union-all-performance/
__________________

Query:

SELECT '01' as Number UNION ALL
SELECT '02' UNION ALL
SELECT '03'
__________________

Output:

Number (column name)
01
02
03
__________________

Main query application:

UNION ALL was used in the cartesian product statements to specify the strings used by the SUBSTRING_INDEX function (discussed further below) to find the local printers in the raw vbscript output. The query below is based on this statement from the main query:

SELECT '* USB' as Port_Type UNION ALL
SELECT '* DOT' UNION ALL
SELECT '* LPT'

Just FYI, many of my example queries use an inline view populated with UNION ALL'd SELECT statements to create a temporary table which is then fed to the other string functions and data manipulation statements in the main SELECT statement. As I mentioned before, this is definitely a handy construction for creating example queries with real data that you can run on your own K1000 (or in MySQL Query Browser).
__________________

Query:

SELECT '* USB' as Port_Type UNION ALL
SELECT '* DOT' UNION ALL
SELECT '* LPT'
__________________

Output:

Port_Type (column name)
* USB
* DOT
* LPT
__________________________________________________________________________________
__________________________________________________________________________________

(10) DISTINCT
__________________________________________________________________________________

I've used this in several other queries and it's extremely useful for when you encounter duplicates in your query results. When that happens, this should be the first word that pops into your mind.

For the full reference manual write-up on this, please see:

http://dev.mysql.com/doc/refman/5.0/en/select.html
__________________

Syntax:

SELECT DISTINCT column(s)
__________________

Explanation:

Removes duplicates from results. Note that this does not sort in any particular order, the output is simply populated by the first unique instance of each distinct row.
__________________

Query:

SELECT DISTINCT Number as 'Numbers'
FROM
(SELECT '01' as Number UNION ALL
 SELECT '02' UNION ALL
 SELECT '02' UNION ALL
 SELECT '01' UNION ALL
 SELECT '03' UNION ALL
 SELECT '02' UNION ALL
 SELECT '04')
InlineView
__________________

Output:

Numbers (column name)
01
02
03
04
__________________

Main query application:

DISTINCT is used to remove duplicate listings of printer and (corresponding) port names, which occur due to (the intentional inclusion of) multiple user profiles in the vbscript output. The trick was figuring out where to put it, as there were multiple SELECT statements.

The query below (seeded this with the actual "raw" local printers output from one of the shared machines) is based on this statement from the main query, which returns distinct results for the MCI.ID and Port_Type columns, as well as the printer names output from the TRIM + SUBSTRING_INDEX statement:

SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
__________________

Query:

SELECT DISTINCT Printer
FROM
(SELECT 'HP LaserJet 6P' as Printer UNION ALL
 SELECT 'HP LaserJet 2100 PCL6' UNION ALL
 SELECT 'hp deskjet 960c' UNION ALL
 SELECT 'HP LaserJet 6P' UNION ALL
 SELECT 'HP LaserJet 2100 PCL6' UNION ALL
 SELECT 'hp deskjet 960c' UNION ALL
 SELECT 'HP LaserJet 6P' UNION ALL
 SELECT 'HP LaserJet 2100 PCL6' UNION ALL
 SELECT 'hp deskjet 960c' UNION ALL
 SELECT 'HP LaserJet P2015 Series PCL 6' UNION ALL
 SELECT 'HP LaserJet P2015 Series PCL 6' UNION ALL
 SELECT 'HP LaserJet P2015 Series PCL 6' UNION ALL
 SELECT 'HP LaserJet P2015 Series PCL 6')
InlineView
__________________

Output:

Printer (column name)
HP LaserJet 6P
HP LaserJet 2100 PCL6
hp deskjet 960c
HP LaserJet P2015 Series PCL 6
__________________________________________________________________________________
__________________________________________________________________________________

(11) GROUP_CONCAT
__________________________________________________________________________________

This one is great for creating lists when there is a many-to-one relationship.

For the full reference manual write-up on this, please see:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
__________________

Syntax:

GROUP_CONCAT(expression [SEPARATOR string_value])

Note that there are other variations, but this is the one I'm using below.
__________________

Explanation:

Concatenates (joins) specified strings with a default delimiter (comma only, no spaces). It ignores non-NULL values and will return NULL if all values are NULL. The delimiter can be changed by adding SEPARATOR, as is done below.
__________________

Query:

SELECT GROUP_CONCAT(Number SEPARATOR ", ") as 'Numbers'
FROM
(SELECT '01' as Number UNION ALL
 SELECT '02' UNION ALL
 SELECT '03' UNION ALL
 SELECT '04')
InlineView
__________________

Output:

Numbers (column name)
01, 02, 03, 04
__________________

Main query application:

GROUP_CONCAT is used (along with the GROUP BY M.NAME statement, which formats the output to list one machine per row) to list all of a machine's printers in a single row. If GROUP_CONCAT were excluded (and the GROUP BY statement used), only the first printer would be listed in the output. Conversely, if the GROUP BY were excluded (and GROUP_CONCAT used), all of the printers would be listed in a single row.

During an earlier version of the query, the "plain" GROUP_CONCAT output had a trailing space, so SEPARATOR ", " is being used (along with TRIM, which knocks off the trailing space) to fix the output in the main query. This is an example of what it looked like before adding SEPARATOR ", " and using TRIM:

HP LaserJet 6P ,HP LaserJet 2100 PCL6 ,hp deskjet 960c ,HP LaserJet P2015 Series PCL 6

The query below is based on this statement from the main query:

GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printers'

On a side note - this query's setup demonstrates how you can create and populate a temporary table with multiple rows and columns. I had a big smile on my face when I got this one working. ^_^
__________________

Query:

SELECT Machine,
GROUP_CONCAT(Printer SEPARATOR ", ") as 'Local Printers'
FROM
(SELECT 'EPROD1' as Machine UNION ALL
 SELECT 'GHOWARD')
InlineViewM,
(SELECT 'HP LaserJet 6P' as Printer UNION ALL
 SELECT 'HP LaserJet 2100 PCL6' UNION ALL
 SELECT 'hp deskjet 960c' UNION ALL
 SELECT 'HP LaserJet P2015 Series PCL 6' UNION ALL
 SELECT 'HP LaserJet 1022' UNION ALL
 SELECT 'HP Deskjet 6940 series')
InlineViewP
WHERE (InlineViewM.Machine = 'EPROD1'
AND InlineViewP.Printer rlike '6P|2100|960c|P2015')
OR (InlineViewM.Machine = 'GHOWARD'
AND InlineViewP.Printer rlike '1022|6940')
GROUP BY Machine
__________________

Output:

Machine Local Printers
EPROD1 HP LaserJet 6P, HP LaserJet P2015 Series PCL 6, HP LaserJet 2100 PCL6, hp deskjet 960c
GHOWARD HP Deskjet 6940 series, HP LaserJet 1022
__________________________________________________________________________________
__________________________________________________________________________________

Section IV - String Manipulation Functions
__________________________________________________________________________________
__________________________________________________________________________________

I provide my own explanations focused on how I used these functions, so be sure to refer to this for more syntax and usage examples:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

As before, here is the main query to save you from scrolling around so much.
__________________

Query:

SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
GROUP_CONCAT(TRIM(LEADING '* ' FROM Port) SEPARATOR ", ") as 'Ports',
GROUP_CONCAT(Printer SEPARATOR ", ") as 'Local Printers'
FROM
(SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')
Filter
JOIN MACHINE M on (M.ID = Filter.ID)
JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE Filter.Printer <> ''
AND UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|univeral|copy 2|copy 3'
AND L.NAME RLIKE 'computers'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
GROUP BY M.NAME
ORDER by Location, Machine
__________________________________________________________________________________
__________________________________________________________________________________

(12) LENGTH
__________________________________________________________________________________

Syntax:

LENGTH(string)
__________________

Explanation:

LENGTH returns the length of the string in bytes (a number). As you will see, this becomes very useful when combined with other string functions (such as SUBSTRING). Below is an example populated with the text for one of my machine labels to show the output that gets generated. It is based on this statement from the main query:

LENGTH(L.NAME)
__________________

Query:

SELECT LENGTH('East Canton computers') as 'Location'
__________________

Output:

Location (column name)
21
__________________
__________________

Main query application:

Since LENGTH returns a number, you can use it to remove a specified number of characters by following it with a subtraction operation. Read on to the next function (SUBSTRING) and you'll see why.
__________________

Query:

SELECT LENGTH('East Canton computers') - 10 as 'Location'
__________________

Output:

Location (column name)
11
__________________________________________________________________________________
__________________________________________________________________________________

(13) SUBSTRING
__________________________________________________________________________________

Syntax:

SUBSTRING(string, position, length)

Note that there are other variations, but this is the one I'm using below.
__________________

Explanation:

SUBSTRING returns part of a string (as long as specified by "length"), starting from the specified "position". "1" in the position field indicates the first character of the string. It's ideal for when you are dealing with a consistently sized string you want to extract (or remove, as I discuss next).
__________________

Query:

SELECT SUBSTRING('MySQL has many string functions', 11, 4) as 'Substring'
__________________

Output:

Substring (column name)
many
__________________
__________________

Main query application:

I'm combining the LENGTH function with SUBSTRING to knock the string " computers" off from the *end* of my "computers" label names, so I can use them to populate the Location column. Since the lengths of the label names varies (i.e. East Canton computers, Pittsburgh computers, etc), populating the length field with L.NAME - 10 ensures the output will be as desired. On a side note, if I hadn't been dealing with a variable number of spaces in the label names, I would have used the SUBSTRING_INDEX function instead (with ' ' as the delimiter) instead of SUBSTRING.

The query below is based on this statement from the main query:

SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location'
__________________

Query:

SELECT SUBSTRING(Label, 1, LENGTH(Label) - 10) as 'Location'
FROM
(SELECT 'East Canton computers' as Label UNION ALL
 SELECT 'Oak Hill computers' UNION ALL
 SELECT 'Pittsburgh computers' UNION ALL
 SELECT 'Santa Fe Springs computers')
InlineView
__________________

Output:

Location (column name)
East Canton
Oak Hill
Pittsburgh
Santa Fe Springs
__________________________________________________________________________________
__________________________________________________________________________________

(14) SUBSTRING_INDEX
__________________________________________________________________________________

Syntax:

SUBSTRING_INDEX(string, delimiter, count)
__________________

Explanation:

SUBSTRING_INDEX counts the instances of the specified delimiter and then returns the part of the string to the left (if count is positive) or right (if count is negative) of the final delimiter.
__________________

Query:

SELECT SUBSTRING_INDEX('MySQL has many string functions', ' ', 3) as 'Substring'
__________________

Output:

Substring (column name)
MySQL has many
__________________
__________________

Nesting SUBSTRING_INDEX statements:

SUBSTRING_INDEX statements can be nested to extract an inner substring. This is the same idea as the SUBSTRING function, but much more flexible as you aren't forced to define a specific length for the substring.
__________________

Query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('MySQL has many string functions', ' ', 3), ' ', -1) as 'Substring'
__________________

Output:

Substring (column name)
many
__________________
__________________

Main query application:

Using nested SUBSTRING_INDEX statements is what I finally got to work for extracting the local printer name from the output for the Default Printers vbscript. I tried various combinations of SUBSTRING and LOCATION (as a few forums suggested as a possibility), but (as evidenced above) these can get rather long in comparison and I honestly didn't have any luck getting them to knock the front *and* back off of the substring (the closest I got was an empty field, which seemed to indicate the statement wasn't supported by the version of MySQL on the K1000). At any rate, the query below is based on this statement from the main query:

SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), " ", -1) as printer
__________________

Query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(example, "* USB", 1), " ", -1) as 'Local Printer'
FROM
(SELECT '8/13/2012 9:04:09 AM - Logged in user: bcarter ------------------------------------------------------ Santa Fe Lanier LD145 * IP_192.168.4.253 * LANIER LD145 PCL 6 * Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP Officejet 6500 E710n-z * USB002 * HP Officejet 6500 E710n-z * HP Deskjet 6940 series * USB001 * HP Deskjet 6940 series * Fax - HP Officejet 6500 E710n-z * USB003 * Fax - HP Officejet 6500 E710n-z * Fax * SHRFAX: * Microsoft Shared Fax Driver * Adobe PDF * My Documents\*.pdf * Adobe PDF Converter * My Documents ' AS example)
InlineView
__________________

Output:

Local Printer (column name)
HP Officejet 6500 E710n-z
__________________________________________________________________________________
__________________________________________________________________________________

(15) TRIM
__________________________________________________________________________________

Syntax:

TRIM(string)

Note that there are other variations, but this is the one I'm using below.
__________________

Explanation:

TRIM removes any leading and trailing spaces from a string.
__________________

Query:

SELECT TRIM(' This string will have leading and trailing spaces removed. ') as 'Trimmed'
__________________

Output:

Trimmed (column name)
This string will have leading and trailing spaces removed.
__________________

Main query application:

Although this wasn't apparent at first, the printer names output by the SUBSTRING_INDEX statement have a trailing space on the end (go ahead and run the query above and/or check the output for yourself, I left the trailing space there). Running GROUP_CONCAT made this clear, as applying that to the parsed printer names resulted in unexpected output (which TRIM resolved). The query below is based on this statement from the main query:

TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), " ", -1)) as printer
__________________

Query:

SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(example, "* USB", 1), " ", -1)) as 'Local Printer'
FROM
(SELECT '8/13/2012 9:04:09 AM - Logged in user: bcarter ------------------------------------------------------ Santa Fe Lanier LD145 * IP_192.168.4.253 * LANIER LD145 PCL 6 * Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP Officejet 6500 E710n-z * USB002 * HP Officejet 6500 E710n-z * HP Deskjet 6940 series * USB001 * HP Deskjet 6940 series * Fax - HP Officejet 6500 E710n-z * USB003 * Fax - HP Officejet 6500 E710n-z * Fax * SHRFAX: * Microsoft Shared Fax Driver * Adobe PDF * My Documents\*.pdf * Adobe PDF Converter * My Documents ' AS example)
InlineView
__________________

Output:

Local Printer (column name)
HP Officejet 6500 E710n-z
__________________________________________________________________________________
__________________________________________________________________________________

Section V - Subquery Analysis and Variations
__________________________________________________________________________________
__________________________________________________________________________________

(16) Cartesian_Product Subquery Analysis and Variations
__________________________________________________________________________________

1) Cartesian_Product Subquery
__________________

The A, B and C inline views (noncorrelated subqueries) create a temporary ("3 port types * 3 * 3" cartesian product) table, which is in turn used by the Temp_Table_to_Populate_Substring_Index subquery to generate a "counter" table. Note that the rows are ordered by the A inline view.
__________________

Query:

SELECT *
FROM
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
A,
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
B,
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
C
ORDER BY A.Port_Type
__________________

Output (3*3*3 Cartesian Product):

(A) (B) (C)
Port_Type Port_Type Port_Type
* DOT * USB * LPT
* DOT * USB * DOT
* DOT * USB * USB
* DOT * LPT * LPT
* DOT * LPT * DOT
* DOT * LPT * USB
* DOT * DOT * LPT
* DOT * DOT * DOT
* DOT * DOT * USB
* LPT * DOT * LPT
* LPT * DOT * DOT
* LPT * DOT * USB
* LPT * USB * LPT
* LPT * USB * DOT
* LPT * USB * USB
* LPT * LPT * LPT
* LPT * LPT * DOT
* LPT * LPT * USB
* USB * USB * USB
* USB * LPT * LPT
* USB * LPT * DOT
* USB * LPT * USB
* USB * DOT * LPT
* USB * DOT * DOT
* USB * DOT * USB
* USB * USB * LPT
* USB * USB * DOT
____________________________________________
____________________________________________

2) Cartesian_Product Subquery (variation 1 - smaller cartesian product)
__________________

For demonstration purposes, this is the same as the previous subquery but only uses two inline views to create a temporary ("3 port types * 3" cartesian product) table.
__________________

Query:

SELECT *
FROM
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
A,
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
B
ORDER BY A.Port_Type
__________________

Output (3*3 Cartesian Product):

(A) (B)
Port_Type Port_Type
* DOT * LPT
* DOT * USB
* DOT * DOT
* LPT * LPT
* LPT * USB
* LPT * DOT
* USB * USB
* USB * DOT
* USB * LPT
____________________________________________
____________________________________________

3) Cartesian_Product Subquery (variation 2 - no cartesian product)
__________________

This is the same as the previous subquery but with only one inline view (and thus no cartesian product). Multiple inline views are required to generate a cartesian product.
__________________

Query:

SELECT *
FROM
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
A
ORDER BY A.Port_Type
__________________

Output (No Cartesian Product):

(A)
Port_Type
* DOT
* LPT
* USB
____________________________________________
____________________________________________

4) Cartesian_Product Subquery (variation 3 - cartesian product with two Select statement factors)
__________________

This time the query only has two ports in the inline views.
__________________

Query:

SELECT *
FROM
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT')
A,
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT')
B,
(SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT')
C
ORDER BY A.Port_Type
__________________

Output (2*2*2 Cartesian Product):

(A) (B) (C)
Port_Type Port_Type Port_Type
* DOT * USB * DOT
* DOT * USB * USB
* DOT * DOT * DOT
* DOT * DOT * USB
* USB * USB * USB
* USB * DOT * DOT
* USB * DOT * USB
* USB * USB * DOT

____________________________________________
____________________________________________

5) Cartesian_Product Subquery (variation 4 - only 1 factor = no cartesian product)
__________________

One last variation for the Cartesian_Product Subquery, this time with only one port. This essentially removes the extra "dimension" necessary to create a cartesian product, which results in just one port in each column. Keep in mind this could be "remedied" by adding additional UNION ALL statements to each inline view, as was done in part (8).
__________________

Query:

SELECT *
FROM
(SELECT '* USB' as Port_Type)
A,
(SELECT '* USB' as Port_Type)
B,
(SELECT '* USB' as Port_Type)
C
ORDER BY A.Port_Type
__________________

Output (No Cartesian Product):

(A)
Port_Type
* DOT
* LPT
* USB

__________________________________________________________________________________
__________________________________________________________________________________

(17) Temp_Table_to_Populate_Substring_Index Subquery Analysis and Variations
__________________________________________________________________________________

1) Temp_Table_to_Populate_Substring_Index Subquery
__________________

This subquery creates a temporary "counter" table that is used to "feed" the Substring_Index statements, allowing them to "loop" through the target machine's MCI.STR_FIELD_VALUE string (that beast at the beginning of this article) and find all local printer instances. Only the A column from the cartesian product table is needed, as the intent is to:

1) Create a Counter column.
2) Use the Counter and Port_Type columns to populate the SUBSTRING_INDEX statement in the upper query.

The Port_Type column is used to populate the @l:=Port_Type column (basically a "dummy" column, whose sole purpose is used to help create a condition conducive to counting), and in turn these columns are used by the IF statement to populate the Counter column. The IF statement basically says "if the Port_Type column's field matches @l (which has already been set to equal Port_Type by the third column statement, @l:=Port_Type), then increment @i (which is initialized at 0 in the Define_Variables subquery)" and this number is assigned to @i (which is then aliased to "Counter"). Remember the point about being able to assign and read a variable at the same time in MySQL, and hopefully this will make more sense.

The Define_Variables subquery is (implicitly) joined to the Cartesian_Product subquery, so the main query (here) can use @l (which is initialized without any value) to pull the Port_Type data from the (temporary) cartesian product table (as listed above) in a kind of loop (that ends when the IF statement runs out of data from the cartesian product table).

Sorry for all of the parentheses, it's how I think (in subqueries)... If it's still not clear (or you'd like to see another example), I *highly* recommend reading this:

http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/
__________________

Query:

SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
FROM
(SELECT A.Port_Type
FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
C
ORDER BY A.Port_Type)
Cartesian_Product,
(SELECT @i:=0,@l:='')
Define_Variables
__________________

Output:

(A)
Port_Type Counter @l:=Port_Type
* DOT 1 * DOT
* DOT 2 * DOT
* DOT 3 * DOT
* DOT 4 * DOT
* DOT 5 * DOT
* DOT 6 * DOT
* DOT 7 * DOT
* DOT 8 * DOT
* DOT 9 * DOT
* LPT 1 * LPT
* LPT 2 * LPT
* LPT 3 * LPT
* LPT 4 * LPT
* LPT 5 * LPT
* LPT 6 * LPT
* LPT 7 * LPT
* LPT 8 * LPT
* LPT 9 * LPT
* USB 1 * USB
* USB 2 * USB
* USB 3 * USB
* USB 4 * USB
* USB 5 * USB
* USB 6 * USB
* USB 7 * USB
* USB 8 * USB
* USB 9 * USB
____________________________________________
____________________________________________

2) Temp_Table_to_Populate_Substring_Index Subquery (alternate variation)
__________________

This is the approach I took when creating examples for explaining several of the functions earlier. It is ideal for times when...

1) You only have a varied data set you want to use to populate a temporary table
2) You want/need to have more granular control over the number of instances you want/need to target (between what cartesian products will produce, for example)
3) You would prefer to avoid cartesian products altogether

..this type of query will produce the same columns needed to populate the SUBSTRING_INDEX statement in the upper query. Another approach would probably involve using the user defined variables in a query to generate a temporary counter table (just tossing the idea out there, as I haven't played with it myself... yet). Just remember that regular KBOX users/owners won't be able to do something like this due to lack of permissions to the database:

create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

..Error 1142 - INSERT command denied to user 'R1'@'x.x.x.x' for table 'integers'
__________________

Query:

SELECT 1 as Counter, '* DOT' as Port_Type UNION ALL
SELECT 2 as Counter, '* DOT' UNION ALL
SELECT 3 as Counter, '* DOT' UNION ALL
SELECT 4 as Counter, '* DOT' UNION ALL
SELECT 5 as Counter, '* DOT' UNION ALL
SELECT 6 as Counter, '* DOT' UNION ALL
SELECT 7 as Counter, '* DOT' UNION ALL
SELECT 8 as Counter, '* DOT' UNION ALL
SELECT 9 as Counter, '* DOT' UNION ALL
SELECT 1 as Counter, '* LPT' UNION ALL
SELECT 2 as Counter, '* LPT' UNION ALL
SELECT 3 as Counter, '* LPT' UNION ALL
SELECT 4 as Counter, '* LPT' UNION ALL
SELECT 5 as Counter, '* LPT' UNION ALL
SELECT 6 as Counter, '* LPT' UNION ALL
SELECT 7 as Counter, '* LPT' UNION ALL
SELECT 8 as Counter, '* LPT' UNION ALL
SELECT 9 as Counter, '* LPT' UNION ALL
SELECT 1 as Counter, '* USB' UNION ALL
SELECT 2 as Counter, '* USB' UNION ALL
SELECT 3 as Counter, '* USB' UNION ALL
SELECT 4 as Counter, '* USB' UNION ALL
SELECT 5 as Counter, '* USB' UNION ALL
SELECT 6 as Counter, '* USB' UNION ALL
SELECT 7 as Counter, '* USB' UNION ALL
SELECT 8 as Counter, '* USB' UNION ALL
SELECT 9 as Counter, '* USB'
__________________

Output:

Counter Port_Type
1 * DOT
2 * DOT
3 * DOT
4 * DOT
5 * DOT
6 * DOT
7 * DOT
8 * DOT
9 * DOT
1 * LPT
2 * LPT
3 * LPT
4 * LPT
5 * LPT
6 * LPT
7 * LPT
8 * LPT
9 * LPT
1 * USB
2 * USB
3 * USB
4 * USB
5 * USB
6 * USB
7 * USB
8 * USB
9 * USB
__________________________________________________________________________________
__________________________________________________________________________________

(18) Filter Subquery Analysis and Variations
__________________________________________________________________________________

1) Filter Subquery
__________________

This subquery lists each Machine Custom Inventory ID, port and local printers (including blank listings). The blank listings correspond to the DISTINCT statement condensing all empty results, as the following examples will illustrate. I added the "ORDER BY MCI.ID" statement to the original so the machines would be listed in sequence, which helps make things a little easier to follow (for us humans).
__________________

Query:

SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
(SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
Cartesian_Product,
 (SELECT @i:=0,@l:='')
Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
ORDER BY MCI.ID
__________________

Example Output:

ID Port Printer
54 * DOT HP LaserJet 1300
54 * DOT
54 * LPT
54 * USB HP DeskJet 812C
54 * USB HP Deskjet 6980 series
54 * USB
183 * DOT
183 * LPT HP LaserJet 4000
183 * LPT
183 * USB HP Photosmart C3100 series
183 * USB
367 * DOT
367 * LPT
367 * USB HP LaserJet P2015
367 * USB
369 * DOT hp officejet k series fax
369 * DOT hp officejet k series
369 * DOT
369 * LPT
369 * USB
____________________________________________
____________________________________________

2) Filter Subquery (variation 1 - all output for one machine)
__________________

I removed DISTINCT (to show all results) and limited the query to a single MCI.ID number (690 - a machine with 8 printers to illustrate later how it will get truncated if the cartesian product is too small). Note in the output that there are 9 rows for each type (3 port types * 3 * 3), which allows for 9 potential printers of each type.
__________________

Query:

SELECT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
(SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
Cartesian_Product,
 (SELECT @i:=0,@l:='')
Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
AND MCI.ID = 690
ORDER BY MCI.ID
__________________

Example output:

ID Port Printer
690 * DOT
690 * DOT
690 * DOT
690 * DOT
690 * DOT
690 * DOT
690 * DOT
690 * DOT
690 * DOT
690 * LPT
690 * LPT
690 * LPT
690 * LPT
690 * LPT
690 * LPT
690 * LPT
690 * LPT
690 * LPT
690 * USB Lexmark Pro700 Series (USB)
690 * USB Lexmark Pro200 Series (USB)
690 * USB HP Officejet Pro 8500 A909g (USB)
690 * USB HP Officejet 5600 series Warren
690 * USB HP Officejet 5600 series
690 * USB HP Deskjet 460 Series
690 * USB Dell V510 Series (USB)
690 * USB Dell AIO Printer A960
690 * USB
____________________________________________
____________________________________________

3) Filter Subquery 1 (variation 2 - all output for one machine, smaller cartesian product)
__________________

Same as variation 1, but only used 2 statements for the cartesian product. Note in the output that there's now 3 rows for each type (3 port types * 3), which allows for 3 potential printers of each type (no further matches would be made).
__________________

Query:

SELECT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
(SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B
 ORDER BY A.Port_Type)
Cartesian_Product,
 (SELECT @i:=0,@l:='')
Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
AND MCI.ID = 690
ORDER BY MCI.ID
__________________

Example output:

ID Port Printer
690 * DOT
690 * DOT
690 * DOT
690 * LPT
690 * LPT
690 * LPT
690 * USB Lexmark Pro700 Series (USB)
690 * USB Lexmark Pro200 Series (USB)
690 * USB HP Officejet Pro 8500 A909g (USB)
____________________________________________
____________________________________________

4) Filter Subquery 1 (variation 3 - all output for one machine, no cartesian product)
__________________

Same as variation 1, but without the cartesian product. Note in the output that there's now only 1 row for each type (no cartesian product, just the 3 port types), which only allows for 1 potential printer of each type (no further matches would be made).
__________________

Query:

SELECT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
(SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A
 ORDER BY A.Port_Type)
Cartesian_Product,
 (SELECT @i:=0,@l:='')
Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
AND MCI.ID = 690
ORDER BY MCI.ID
__________________

Example output:

ID Port Printer
690 * DOT
690 * LPT
690 * USB Lexmark Pro700 Series (USB)
____________________________________________
____________________________________________

5) Filter Subquery 1 (variation 4 - removing empty rows)
__________________

Same as variation 1, but with the subquery nested so that the Printer column (alias) could be called as a column. This was done so a statement could be added to drop the rows without a printer - WHERE R.printer <> ''. Note how the output lists each MCI.ID, port and printers, without blank listings. Also note that although the main query is able to pull ID, Port and Printer columns without having to use the Variation_4 subquery (inline view) alias, other statements (like WHERE) require the TABLE.COLUMN syntax in order to work.
__________________

Query:

SELECT ID, Port, Printer
FROM
(SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
ORDER BY MCI.ID)
Variation_4
WHERE Printer <> ''
__________________

Example Output:

ID PORT Printer
54 * DOT HP LaserJet 1300
54 * USB HP DeskJet 812C
54 * USB HP Deskjet 6980 series
183 * LPT HP LaserJet 4000
183 * USB HP Photosmart C3100 series
367 * USB HP LaserJet P2015
369 * DOT hp officejet k series fax
369 * DOT hp officejet k series
375 * USB HP LaserJet P2015
376 * DOT hp LaserJet 1320 PCL 6
__________________________________________________________________________________
__________________________________________________________________________________

Section VI - Full Query Analysis and Variations
__________________________________________________________________________________
__________________________________________________________________________________

(19) Original Query
__________________________________________________________________________________

I'm including my original query so you can see a "light" version of the full query (without all of the inline views and temporary tables which enable finding *all* of the local printers). As mentioned previously, this one targets the Default Printers custom inventory field (6913 on my K1000) and only makes a single match (the first), so if a machine's local printer isn't the default printer it won't be included in the results.

The one subquery is a correlated subquery, evidenced by the fact that it can't run by itself and must join on the MACHINE table in order to execute. Not nearly as sophisticated, but the foundation and focus are in place. For more details on the statements used in this query, please see the General Sequence of Events and Focusing the Query sections of part (20).
__________________

Query:

SELECT DISTINCT M.NAME AS 'Machine', M.USER as 'User',
SUBSTRING(L.NAME, 1, length(L.NAME) - 10) as 'Location',
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, '*', 1), " ", -1)
FROM MACHINE_CUSTOM_INVENTORY MCI
WHERE MCI.ID = M.ID and MCI.SOFTWARE_ID = 6913) as 'Local Printer'
FROM MACHINE M
JOIN MACHINE_CUSTOM_INVENTORY MCI on (MCI.ID = M.ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE
MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt'
AND L.NAME rlike 'computers'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
ORDER BY Location, Machine
__________________

Example Output:

Machine User Location Local Printer
EWALLACE ewallace East Canton HP LaserJet P2015 Series PCL 6
eshaffer eshaffer East Canton HP LaserJet P2015dn
mdunn mdunn East Canton HP Officejet Pro 8000 A809 Series
gjones gjones Greensboro hp LaserJet 1320 PCL 5e
FSMITH fsmith Hammond HP Photosmart C3100 series
JWALKER jwalker Hammond HP LaserJet Professional CM1410 Series PCL 6
KRICE krice Hammond hp LaserJet 1000
__________________________________________________________________________________
__________________________________________________________________________________

(20) Original Query Variations
____________________________________________

Reduced Query
__________________

Only pulls the machine name and the printer (from Default printers). I used this in prepping the query for the next variation, which I posted in order to get assistance with building the more advanced query.
__________________

SELECT M.NAME as 'Machine',
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, '*', 1), " ", -1) as 'Local Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID)
WHERE MCI.ID=M.ID and MCI.SOFTWARE_ID = 6913
AND MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt'
ORDER BY Machine
__________________

Example Output:

Machine Local Printer
AALLEN HP LaserJet P2015
ABOYD HP LaserJet 1100 (MS)
ADAVIS HP Photosmart C309a series
AJONES HP LaserJet P2015
ALEWIS HP Officejet 6300 series
APRICE hp LaserJet 1320 PCL 6
ATHOMAS hp LaserJet 1320 PCL 6
____________________________________________
____________________________________________

Reduced Query with Example String Inline
__________________

I used this query earlier - this is the further reduced query that I actually posted, with an actual string inline (containing multiple local printers and showing the statement I used to pull the first local printer). I constructed this using the following example as a guideline (even though I'm not using the same functions as this construct generated blank results on the K1000):

SELECT SUBSTRING(sentence,LOCATE(' ',sentence),LOCATE(' ',sentence,(LOCATE(' ',sentence)+1))-LOCATE(' ',sentence))
FROM (SELECT 'THIS IS A TEST' AS sentence) temp

I thought this may be useful for others looking to post examples so others can run a query with actual working data, rather than talking in abstracts. I know I'll be keeping it around.
__________________

Query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(example, "* USB", 1), " ", -1) as 'Local Printer'
FROM
(SELECT '8/13/2012 9:04:09 AM - Logged in user: bcarter ------------------------------------------------------ Santa Fe Lanier LD145 * IP_192.168.4.253 * LANIER LD145 PCL 6 * Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * HP Officejet 6500 E710n-z * USB002 * HP Officejet 6500 E710n-z * HP Deskjet 6940 series * USB001 * HP Deskjet 6940 series * Fax - HP Officejet 6500 E710n-z * USB003 * Fax - HP Officejet 6500 E710n-z * Fax * SHRFAX: * Microsoft Shared Fax Driver * Adobe PDF * My Documents\*.pdf * Adobe PDF Converter * My Documents ' AS example)
InlineView
__________________

Output:

Local Printer (column name)
HP Officejet 6500 E710n-z
__________________________________________________________________________________
__________________________________________________________________________________

(21) Fully-Operational Query and Analysis
__________________________________________________________________________________

Version 1 (Production)
__________________

I adjusted the indenting (to suit my own personal preferences) and changed most of the aliases, in order to make things a little easier to follow/understand. In order to make this a little more concise, inline view aliases are not being used in the column names (which isn't really necessary once you understand how they work). This is version I'm currently using on my K1000 (as opposed to the version in my last blog, since having the ports listed is handy).
__________________

Query:

SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
GROUP_CONCAT(TRIM(LEADING '* ' FROM Port) SEPARATOR ", ") as 'Ports',
GROUP_CONCAT(Printer SEPARATOR ", ") as 'Local Printers'
FROM
(SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')
Filter
JOIN MACHINE M on (M.ID = Filter.ID)
JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE Filter.Printer <> ''
AND UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|univeral|copy 2|copy 3'
AND L.NAME RLIKE 'computers'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
GROUP BY M.NAME
ORDER by Location, Machine
__________________

Example Output:

Machine User Location Ports Local Printers
EWALLACE ewallace East Canton LPT, LPT, LPT, USB HP LaserJet 6P, HP LaserJet 210... etc
eshaffer eshaffer East Canton USB HP LaserJet P2015dn
GHOWARD GHOWARD East Canton USB, USB HP LaserJet 1022, HP Deskjet 69... etc
mdunn mdunn East Canton USB HP Officejet Pro 8000 A809 Series
TGREEN tgreen East Canton DOT, USB, USB HP LaserJet 4000, HP Photosmart... etc
gjones gjones Greensboro DOT, USB hp LaserJet 1320 PCL 5e, hp des... etc
dsmith dsmith Hammond LPT, USB, USB Brother HL-2040 series, HP Desk... etc
____________________________________________
____________________________________________

Version 2 (Inline View Alias Clarification)
__________________

Intended for clarification and learning purposes only, all inline view aliases have been added to column names in the outer queries in order to make the source of columns easier to trace. Output is *exactly* the same as Version 1.
__________________

Query:

SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
GROUP_CONCAT(TRIM(LEADING '* ' FROM Filter.Port) SEPARATOR ", ") as 'Ports',
GROUP_CONCAT(Filter.Printer SEPARATOR ", ") as 'Local Printers'
FROM
(SELECT DISTINCT MCI.ID, Temp_Table_to_Populate_Substring_Index.Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE,
Temp_Table_to_Populate_Substring_Index.Port_Type,
Temp_Table_to_Populate_Substring_Index.Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Cartesian_Product.Port_Type,
 @i:=IF(Cartesian_Product.Port_Type=@l, @i+1, 1) as Counter,
 @l:=Cartesian_Product.Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')
Filter
JOIN MACHINE M on (M.ID = Filter.ID)
JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE Filter.Printer <> ''
AND UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|univeral|copy 2|copy 3'
AND L.NAME RLIKE 'computers'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
GROUP BY M.NAME
ORDER by Location, Machine
____________________________________________
____________________________________________

General Sequence of Events
__________________

I can't *guarantee* the exact order of things here, since the query optimizer has the last say on that, but this should be relatively accurate as the inline views force the order of execution for the subqueries (which are all uncorrelated, as inline views tend to be). My goal here is to "tell a story" of how this query works, so the construction and (thought) process can be understood (at a higher level). For cold, hard details, please feel free to analyze the query and comprising subqueries using EXPLAIN and other MySQL analytical tools. If further clarification is needed on the subqueries, please refer back to the Subquery Analysis and Variations section. My ultimate hope is that this "story" will tie all of the earlier/later explanations/examples together, providing a solid understanding of this query, how it was constructed and how it "breathes".

1) The Cartesian_Product (and nested A, B & C) subqueries generate a (temporary) cartesian product table and orders it by port name.

2) The Define_Variables subquery initializes the user defined variables.

3) The Temp_Table_to_Populate_Substring_Index subquery uses the cartesian product table and the conditional user defined variable statement to create a (temporary) "counter" table consisting of nine instances of each port name in one column, and a corresponding count (1 - 9) in the other column.

4) The Filter subquery selects the port column (from the "counter" table) and the custom inventory ID column (to enable the corresponding WHERE statement). It focuses on the custom inventory string fields value column, only selecting (a) those with local printer ports listed and (b) in custom inventory software item 6560. Next it feeds the "counter" table data into the TRIM/SUBSTRING_INDEX string manipulation statement (Printer), which applies the table to each selected string field looking for matches, and formats any corresponding matches as specified. These matches (printer names) are then inserted into another (temporary) table consisting of three columns (custom inventory ID, fully parsed printer names that corresponds to the machine's custom inventory ID, and port names that correspond to the printers) with DISTINCT applied to filter out any repetition of printer names and corresponding ports.

5) The main query adds columns for machine name, user name and location, joining corresponding tables as necessary. It filters out rows from the Filter table based on absence of printer name (non-matches in the "counter" table), existence of certain terms in the printer name, label membership and machine name. Rows are grouped by machine name, with printer and port names taken from the Filter table and concatenated/formatted further along with the (location) label names. Results are then ordered by location and machine name, then output as listed above.
____________________________________________
____________________________________________

Focusing the Query
__________________

Aside from forcing the subquery execution order via inline views, one of the other important aspects contributing to this query running fairly quickly (~1.0875 seconds according to the MySQL Query Browser, despite parsing some monster string fields) is the use of very focused WHERE statements. This prevents massive full-table scans which would drag down performance severely.
____________________________________________
____________________________________________

1) Filter Subquery
__________________

WHERE MCI.SOFTWARE_ID = 6560

This statement excludes all of the other Machine Custom Inventory Software ID fields so that when the Temp_Table_to_Populate_Substring_Index subquery is fed field data, it is *only* coming from this specific ID. This could be excluded and the results filtered later (when the @i:=IF statement finds no ports and sets the counter to 1), but it makes much more sense to filter this ahead of time.

This is also the number you'll want to change to match your own corresponding MCI.SOFTWARE_ID "All Printers" fields.
__________________

(WHERE) MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'

This statement excludes any machines that don't have "usb", "dot" or "lpt" in their Machine Custom Inventory String Field Value field, so that only machines with printers on local ports will be included. Again, it makes sense to filter this in the subquery as we are already referencing this table anyways, and since we want to pass as little extraneous data as necessary to the string manipulation functions.
____________________________________________
____________________________________________

2) Main Query
__________________

WHERE Filter.Printer <> ''

This (and the following statements) are basically presentation filters that drop rows (based on certain criteria) so they don't appear in the final report. In this case, all of the blank "printers" that resulted from non-matched cartesian product table fields are excluded. For an example of the blank fields, jump back up to part (18) Filter Subquery Analysis and Variations and take a look at the variations, particularly the last one where this statement is added.
__________________

(WHERE) UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|universal|copy 2|copy 3'

I was very happy with this statement, almost as happy as with the inline views. The UPPER says "ignore the case of the printer name string", while the REGEX (rlike) statement says "drop all printers with fax, pdf, etc in their names". This allowed me to exclude quite a number of all-in-one fax printers, application "software" printers (that claimed to use LPT1) and local printers that were shared from machines and installed on others (which reported the local port from the sharing machine). Mine is expanded a bit more to include machine names, but there should be enough here to get you started. I would just recommend running an open query first and figuring out exactly what you'll want to exclude using this. For example, I'm not filtering out "copy 1" as some machines are missing the first install instance.
__________________

(WHERE) L.NAME RLIKE 'computers'

This statement targets labels with the string "computers" in the name. I could have used LIKE '%computers%' instead, but I personally prefer using RLIKE instead of LIKE as it's easy to later adjust conditions as needed (i.e. RLIKE 'client|laptop'). Honestly, at this point this statement is just a placeholder for future filtering, as it includes *all* of my "computers" labels (which includes all of the machines on the K1000). But later on, it will be quite easy to target more distinct groups just by changing 'computers' to something like 'Pittsburgh|client|laptop'.

A side note - my "computers" labels is just a list of all of the locations where I have machines, and includes all of the machines at those locations. For example, "Pittsburgh computers" will include all of the computers in the Pittsburgh site. I often reference these as I like to filter, list and sort on location in my reports. If you would like to see the way these labels are setup, please refer to this article:

K1000 Reports - Machine Lists and Counts by Site, Role & Type
http://www.itninja.com/blog/view/k1000-reports-machine-lists-and-counts-by-site-role-type

After I finish with this article, I plan on doing one on K1000 labels, so keep an eye out if that sounds useful.
__________________

(WHERE) M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'

This statement is a great way to drop machines completely out of the report. In my case, I have some computers that must have LPT1 mapped to a network printer in order for a legacy application to be able to print. Since these aren't really local printers and because there are no other local printers installed on these machines (and won't be), I'm explicitly excluding them from the results using this statement. As with the previous REGEX (RLIKE) statement, this is very easy to tweak as needed (for review and adding exclusions).
__________________________________________________________________________________
__________________________________________________________________________________

(22) Fully-Operational Query Variations
__________________________________________________________________________________

1) Machine, Printer and Ports Only
__________________

For those with no need for users or locations, this should do the trick.
__________________

SELECT M.NAME as 'Machine',
GROUP_CONCAT(TRIM(LEADING '* ' FROM Port) SEPARATOR ", ") as 'Ports',
GROUP_CONCAT(Printer SEPARATOR ", ") as 'Local Printers'
FROM
(SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')
Filter
JOIN MACHINE M on (M.ID = Filter.ID)
WHERE Filter.Printer <> ''
AND UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|univeral|copy 2|copy 3'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
GROUP BY M.NAME
ORDER by Machine
__________________

Example Output:

Machine Ports Local Printers
AALLEN USB HP LaserJet P2015
ABOYD LPT HP LaserJet 1100 (MS)
ADAVIS USB, USB HP Photosmart C309a series, HP Deskjet F4500 series
AFOX LPT TEC B-872
AJONES USB, USB HP LaserJet P2015, HP LaserJet 1022
ALEWIS USB, USB HP Officejet 6300 series, Canon i70
AKID USB Canon MF6500 Series UFRII LT
____________________________________________
____________________________________________

2) Line-Item for Each Local Printer
__________________

This version drops the GROUP-CONCAT and GROUP-BY functions so each printer and its corresponding port are listed on its own row. Other than that, it's the same as the original version.
__________________

Query:

SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
TRIM(LEADING '* ' FROM Port) as 'Ports',
Printer as 'Local Printers'
FROM
(SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')
Filter
JOIN MACHINE M on (M.ID = Filter.ID)
JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE Filter.Printer <> ''
AND UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|univeral|copy 2|copy 3'
AND L.NAME RLIKE 'computers'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
ORDER by Location, Machine
__________________

Example Output:

Machine User Location Ports Local Printers
EWALLACE ewallace East Canton LPT HP LaserJet 6P
EWALLACE ewallace East Canton LPT LaserJet 2100 PCL6
EWALLACE ewallace East Canton LPT hp deskjet 960c
EWALLACE ewallace East Canton USB HP LaserJet P2015 Series PCL 6
eshaffer eshaffer East Canton USB HP LaserJet P2015dn
GHOWARD ghoward East Canton USB HP LaserJet 1022
GHOWARD ghoward East Canton USB HP Deskjet 6940 series
____________________________________________
____________________________________________

3) Machine, Printer and Ports-Only Line-Item for Each Local Printer
__________________

A combination of the last two, for anyone who might find them useful. Note the repetition of machine names in the example output - this would indicate multiple local printers on that machine.
__________________

Query:

SELECT M.NAME as 'Machine', TRIM(LEADING '* ' FROM Port) as 'Ports', Printer as 'Local Printers'
FROM
(SELECT DISTINCT MCI.ID, Port_Type as 'Port',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Port_Type, Counter), " ", -1)) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN
 (SELECT Port_Type, @i:=IF(Port_Type=@l, @i+1, 1) as Counter, @l:=Port_Type
 FROM
 (SELECT A.Port_Type
 FROM
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 A,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 B,
 (SELECT '* USB' as Port_Type UNION ALL
 SELECT '* DOT' UNION ALL
 SELECT '* LPT')
 C
 ORDER BY A.Port_Type)
 Cartesian_Product,
 (SELECT @i:=0,@l:='')
 Define_Variables)
Temp_Table_to_Populate_Substring_Index
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')
Filter
JOIN MACHINE M on (M.ID = Filter.ID)
WHERE Filter.Printer <> ''
AND UPPER(Filter.Printer) NOT RLIKE 'fax|pdf|intuit|adp|univeral|copy 2|copy 3'
AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
ORDER by Machine
__________________

Example Output:

Machine Ports Local Printers
AALLEN USB HP LaserJet P2015
ABOYD LPT HP LaserJet 1100 (MS)
ADAVIS USB HP Photosmart C309a series
ADAVIS USB HP Deskjet F4500 series
AFOX LPT TEC B-872
AJONES USB HP LaserJet P2015
AJONES USB HP LaserJet 1022
__________________________________________________________________________________
__________________________________________________________________________________

(X) Conclusion
__________________________________________________________________________________

OK, that was a mouthful and hopefully useful in some way to someone out there! If you think of any good applications for any of this, please be sure to let me know in the comments. I feel like this is just scratching the surface, now that I understand how to use and seed temporary tables.

John
2012/08/28

Comments

Great job, d00d! I just want to clarify something you brought up about database permissions. Within a rule in the K1000, you DO have the ability to use INSERT. In fact, you can do just about anything from a rule. However, with remote connectivity (e.g. MySQL Workbench) you are stuck with read-only - hence the naming of the accounts: R1, R2, R3 (if you have a multi-org box; single-org boxes only have R1)... R = Read-only - that's the naming convention.

So, you have tons of write-access from a ticket rule, but absolutely none from a remote connection like MySQL Workbench.

Keep up the awesome work, John! Wish I could help out around here more, but I'm busy on the Dell Global team! - airwolf 12 years ago
Thanks for tip on custom inventory rules and the clarification on remote connection access, Andy! Any idea if it's possible to create stored procedures and/or user defined functions on the K1000 so they can be accessible when writing SQL reports, etc? I've looked around a bit and haven't seen anything on this.

If I understand the concept well enough, I would imagine I could create a SELECT query that is always true, use an UPDATE query to load the function, run the ticket rule once (manually), then disable it. Guess it's time to do some more research on SQL and see if I can come up with something. ^_^

John - jverbosk 12 years ago

This post is locked

K1000 Reports - Advanced MySQL Query Techniques - Parsing Multiple Substrings from a Field Using Temporary Tables

Comments

Posted by:

View More Blogs from jverbosk

Related Posts

K1000 Reports - Advanced MySQL Query Techniques - Parsing Multiple Substrings from a Field Using Temporary Tables

Comments

Posted by:

Don't be a Stranger!

View More Blogs from jverbosk

Related Posts

Share