MySQL Select Query - Pulling multiple matches from a single field - possible without stored procedures?

I have a field with a long string that I'd like to grab several substrings from.  Grabbing one substring works fine, using a couple of SUBSTRING_INDEX commands to knock the front and the back off.  And I believe I should be able to several queries in a function or procedure to do what I want.  However... I only have read access to the database (it's a Dell KACE K1000).  Is it possible to use a function or procedure to do what I want without using stored procedures or UDFs?

Specifically, here is my current "test" query with an example string:

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

Open in new window


This outputs the first USB printer name, as desired:

HP Officejet 6500 E710n-z

I believe I could grab all of the USB printers by incrementing the count used in the inner SUBSTRING_INDEX, for example:

SUBSTRING_INDEX(SUBSTRING_INDEX(s, "* USB, 2) ...
SUBSTRING_INDEX(SUBSTRING_INDEX(s, "* USB, 3) ...
SUBSTRING_INDEX(SUBSTRING_INDEX(s, "* USB, 4) ...
etc

Open in new window


But I'm not sure how to:

1) Return the results to a single field via CONCAT, with commas ( ,) in between each result

2) Drop the results if they are blank/null or if the string contains "fax" (Fax, FAX, etc)

3) Expand this to also search for "lpt" and "dot" (so there might be 3 different functions or routines, unless variables could be used?)

I believe I'd have to use a function with a DO... WHILE loop, but again I'm not sure if this would be feasible while only have read access to the database.

Just for reference, here is the query I'm currently using to pull the first match (using the table/column names, not an example string):

SELECT M.NAME as 'Machine',
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, '*', 1), "<br/>", -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

Open in new window


Any advise or suggestions would be appreciated, full points to anyone who can possibly get this working with my constraints.

Thanks very much!!!

John
rescoproductsAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
The DISTINCT would go in either:
  a) the SELECT that is generating the 'printer' values; or
  b) the GROUP_CONCAT
SELECT r.name as 'Machine', r.user as 'User', r.location as 'Location',
       GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT DISTINCT m.name, m.user, SUBSTRING(l.name, 1, LENGTH(l.name) - 10) as Location,
             TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(mci.str_field_value, stype, idx), "<br/>", -1)) as printer
      FROM machine_custom_inventory mci
           JOIN machine m ON (m.id = mci.id)
           JOIN machine_label_jt ml ON (m.id = ml.machine_id)
           JOIN label l ON (ml.label_id = l.id)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE mci.software_id = 6560
      AND mci.str_field_value RLIKE 'usb|dot|lpt'
      AND l.name RLIKE 'computers' 
      AND m.name NOT RLIKE 'dgreen|gwhite|wbrown')r
WHERE r.printer <> ''
  AND UPPER(r.printer) NOT RLIKE 'FAX'
GROUP BY r.name, r.user, r.location
ORDER by Location, Machine

-- or

SELECT r.name as 'Machine', r.user as 'User', r.location as 'Location',
       GROUP_CONCAT(DISTINCT printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT m.name, m.user, SUBSTRING(l.name, 1, LENGTH(l.name) - 10) as Location,
             TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(mci.str_field_value, stype, idx), "<br/>", -1)) as printer
      FROM machine_custom_inventory mci
           JOIN machine m ON (m.id = mci.id)
           JOIN machine_label_jt ml ON (m.id = ml.machine_id)
           JOIN label l ON (ml.label_id = l.id)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE mci.software_id = 6560
      AND mci.str_field_value RLIKE 'usb|dot|lpt'
      AND l.name RLIKE 'computers' 
      AND m.name NOT RLIKE 'dgreen|gwhite|wbrown')r
WHERE r.printer <> ''
  AND UPPER(r.printer) NOT RLIKE 'FAX'
GROUP BY r.name, r.user, r.location
ORDER by Location, Machine

-- or, another way to do the JOINs to the other tables.

SELECT m.name as 'Machine', m.user as 'User', SUBSTRING(l.name, 1, LENGTH(l.name) - 10) as 'Location',
       GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT DISTINCT mci.id, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(mci.str_field_value, stype, idx), "<br/>", -1)) as printer
      FROM machine_custom_inventory mci
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE mci.software_id = 6560
      AND mci.str_field_value RLIKE 'usb|dot|lpt'
      AND l.name RLIKE 'computers' 
      AND m.name NOT RLIKE 'dgreen|gwhite|wbrown')r
      -- Now JOIN in the other tables
      JOIN machine m ON (m.id = r.id)
      JOIN machine_label_jt ml ON (m.id = ml.machine_id)
      JOIN label l ON (ml.label_id = l.id)
WHERE r.printer <> ''
  AND UPPER(r.printer) NOT RLIKE 'FAX'
GROUP BY r.name, r.user, SUBSTRING(l.name, 1, LENGTH(l.name) - 10)
ORDER by Location, Machine

Open in new window

0
 
et_meCommented:
You could try below:


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 $$

Open in new window


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

Open in new window


Will return below:


HP Officejet 6500 E710n-z ,HP Deskjet 6940 series 

Open in new window

0
 
lwadwellCommented:
This is a rather clunky fudge to achieve the result I believe.  I did not have 'lpt' or 'dot' example to test with.
select GROUP_CONCAT(printer)
from 
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s, stype, idx), "<br/>", -1) as printer
 FROM (SELECT '8/13/2012 9:04:09 AM - Logged in user: bcarter<br/>------------------------------------------------------<br/>Santa Fe Lanier LD145 * IP_192.168.4.253 * LANIER LD145 PCL 6 * <br/>Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * <br/>Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * <br/>HP Officejet 6500 E710n-z * USB002 * HP Officejet 6500 E710n-z * <br/>HP Deskjet 6940 series * USB001 * HP Deskjet 6940 series * <br/>Fax - HP Officejet 6500 E710n-z * USB003 * Fax - HP Officejet 6500 E710n-z * <br/>Fax * SHRFAX: * Microsoft Shared Fax Driver * <br/>Adobe PDF * My Documents\*.pdf * Adobe PDF Converter * My Documents<br/><br/>' AS s) temp, 
(Select stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
   from (select a.stype
           from (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')a, 
                (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')b, 
                (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')c
          order by a.stype)u,
        (select @i:=0,@l:='',@d)v)y)r
 where r.printer <> ''
   and upper(r.printer) not like '%FAX%'

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
rescoproductsAuthor Commented:
et_me,

Thanks very much, but unfortunately I get an error 1305 - FUNCTION ORG1 strsplit does not exist.  I believe this is due to restricted permissions on the database, which apparently won't allow me to create functions.

John
0
 
rescoproductsAuthor Commented:
lwadwell,

This is exactly what I was hoping might be possible!  Your query runs fine against the sample I provided, however I'm running into a few (hopefully minor) issues when I try to run it against my database:
_________________________________________

1) Would it be possible to adjust the output from this (space, comma after printer):

HP Officejet 6500 E710n-z ,HP Deskjet 6940 series

To this (comma, space after printer):

HP Officejet 6500 E710n-z, HP Deskjet 6940 series
_________________________________________

2) I adjusted your query to run against a column in my database (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE) and noticed that it concats *all* printers, rather than concat'ing results for each row.  Hopefully this makes more sense:

machine1 - printerA, printerB
machine2 - printerC, printerD

Code runs ~~~ output > printerA, printerB, printerC, printerD

Here's the query in it's current form.  Ideally, it only concats for each machine's respective field and would prevent any duplicates in each machine's respective field (which may exist due to multiple user profiles being captured on each machine).

select GROUP_CONCAT(printer)
from 
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1) as printer
FROM MACHINE_CUSTOM_INVENTORY MCI, 
(Select stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
   from (select a.stype
           from (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')a, 
                (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')b, 
                (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')c
          order by a.stype)u,
        (select @i:=0,@l:='',@d)v)y)r
 where r.printer <> ''
   and upper(r.printer) not like '%FAX%'

Open in new window

_________________________________________

3) I need to add two more tables, filter with a few WHERE statements and ORDER by those additional tables.  I tried just adding the MACHINE table, but get an "unknown column" error on M.NAME with this code:

select M.NAME as 'Machine', GROUP_CONCAT(printer)
from 
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1) as 'Local Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID), 
(Select stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
   from (select a.stype
           from (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')a, 
                (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')b, 
                (select 1 x, '* USB' as stype union all select 1, '* lpt' union all select 1, '* dot')c
          order by a.stype)u,
        (select @i:=0,@l:='',@d)v)y)r
 where r.printer <> ''
   and upper(r.printer) not like '%FAX%'

Open in new window


I tried changing the placement of the JOIN statement and creating a separate SELECT statement to add the "Machine" column, but just ended up with more errors.

Ideally, I'd like to include all of the criteria in this 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), "<br/>", -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

Open in new window


Specifically, this would be:

1) JOIN the MACHINE table so I can add columns for "Machine" and "User".
2) JOIN the LABEL table, so I can add a column for "Location"
3) Filter on MACHINE and LABEL columns using WHERE
4) ORDER results by "Location" and "Machine" (name).

Ideally, the output should look something like this:

Machine    User       Location      Local Printer                               
EWALLACE   jbrown     East Canton   HP LaserJet P2015dn, HP DeskJet 1300 series                         
eshaffer   eshaffer   East Canton   HP Deskjet 6940 series                        
GHOWARD    ghoward    East Canton   HP Officejet Pro 8000 A809 Series             
mdunn      mdunn      East Canton   HP Deskjet 6940 series, HP LaserJet P2055 series
TGREEN     tgreen     East Canton   HP LaserJet P2015 Series PCL 6                
gjones     kmiles     Greensboro    hp deskjet 940c, HP LaserJet P2035 series
dsmith     dsmith     Hammond       hp officejet k series   

Open in new window


If having more samples to work with (or more examples, info, etc) would be helpful, please let me know.

Thanks VERY much for your help with this!!!  I'm looking forward to learning from your code, since I just started playing with MySQL for this appliance in the past year and this is by far the most sophisticated problem I've seen so far.

John
0
 
lwadwellCommented:
1. a. The values from the SUBSTRING_INDEX's could probably do with a TRIM() ... this is likely to be the cause of the 'space' before the comma. (http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_trim)
  b. The GROUP_CONCAT defaults to a comma separator - this can be changed (http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat)

2. You will need a GROUP BY.

3. The table you added in is in subquery SELECT ... it will need the column to be
  a) returned by the subquery; and
  b) referenced using the subqueries alias.

Have a look at this version (untested):
SELECT r.NAME as 'Machine', GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT M.NAME, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN MACHINE M on (M.ID = MCI.ID)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* lpt' UNION ALL SELECT 1, '* dot')a, 
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* lpt' UNION ALL SELECT 1, '* dot')c
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* lpt' UNION ALL SELECT 1, '* dot')b, 
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE M.NAME not rlike 'dgreen|gwhite|wbrown')r
WHERE r.printer <> ''
  AND UPPER(r.printer) NOT LIKE '%FAX%'
GROUP BY r.NAME

Open in new window


I have added additional indentation to show the separate SQL's.  I also added a WHERE predicate inside the subquery like you wanted to show the proper placement.
0
 
rescoproductsAuthor Commented:
lwadwell,

Thank you very much for the explanations and references, I truly appreciate it!

We're getting closer, looks very good!  Thanks for indenting the code so I could follow it better.  My MySQL knowledge has all been gained by just tearing apart other examples and making sense of them, so this is outstanding!

I was able to get the MACHINE table added and working, and at this point it is pulling all of the printer info correctly (and formatted correctly thanks to the TRIM you added).  Here's the current working code:

SELECT r.NAME as 'Machine', r.USER as 'User',
GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT M.NAME, M.USER,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN MACHINE M on (M.ID = MCI.ID)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')a, 
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')b,
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt'
      AND M.NAME not rlike 'dgreen|gwhite|wbrown')r
WHERE r.printer <> ''
  AND UPPER(r.printer) NOT RLIKE 'FAX'
GROUP BY r.NAME
ORDER by Machine

Open in new window


And here's a line of example output:

Machine   User     Local Printers
APRICE    aprice   HP LaserJet P2015, HP LaserJet 1022

Open in new window


1) At this point, I'm running into an issue with adding another table (LABEL), due to the column (NAME) already being used in the MACHINE table.  Since everything is referenced at the top with "r" (like r.NAME, r.USER), when I add the LABEL.NAME column and try referencing it as r.NAME I get a duplicate column name error (which makes absolute sense).  I'm just not sure how to get the LABEL.NAME column in the code with everything else there - would it require another SELECT statement or is there a way to alias it differently?

Here's the code to give you an idea of what I'm trying to do (along with all of the subsequent JOIN and WHERE statements that use L.NAME).  The SUBSTRING line is where I'm trying to add it as "Location".

SELECT r.NAME as 'Machine', r.USER as 'User',
SUBSTRING(r.NAME, 1, length(r.NAME) - 10) as 'Location',
GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT M.NAME, M.USER, L.NAME,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN MACHINE M on (M.ID = MCI.ID)
           JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
           JOIN LABEL L ON (ML.LABEL_ID = L.ID)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')a, 
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')b, 
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt'
      AND L.NAME rlike 'computers' 
      AND M.NAME not rlike 'dgreen|gwhite|wbrown')r
WHERE r.printer <> ''
  AND UPPER(r.printer) NOT RLIKE 'FAX'
GROUP BY r.NAME
ORDER by Location, Machine

Open in new window


2) The last thing is the presence of duplicate printers in the output printer field.  For example, here's the current output in the printer field for a multi-user machine:

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

Open in new window


And here's the raw field data that is being parsed by the query, which has the printers listed for each user profile:

8/14/2012 8:04:07 AM - Logged in user:  jcope<br/>------------------------------------------------------<br/>Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * <br/>Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * <br/>HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * <br/>HP LaserJet 6P * LPT1: * HP LaserJet 6P * <br/>HP LaserJet 2100 PCL6 * LPT1: * HP LaserJet 2100 PCL6 * <br/>hp deskjet 960c * LPT1: * hp deskjet 960c * <br/>ctn154 * \\srv-ec01\ctn154 * HP LaserJet P2050 Series PCL6 * <br/>cnt_maint_1320 * \\srv-ec01\cnt_maint_1320 * hp LaserJet 1320 PCL 6 * <br/>\\srv-ec01\cnt_maint_1320 * IP_192.168.5.157 * hp LaserJet 1320 PCL 6 * Maintenance office<br/>\\srv-ec01\HP LaserJet 2100 Series PCL 6 (Copy 1) * IP_192.168.5.154 * HP LaserJet 2100 Series PCL 6 * East Canton<br/>\\srv-ec01\LANIER LD325 * IP_192.168.5.156 * LANIER MP 2510/LD325 PCL 6 * East Canton<br/><br/>8/12/2012 8:03:25 AM - Logged in user:  tcash<br/>------------------------------------------------------<br/>Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * <br/>Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * <br/>HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * <br/>HP LaserJet 6P * LPT1: * HP LaserJet 6P * <br/>HP LaserJet 2100 PCL6 * LPT1: * HP LaserJet 2100 PCL6 * <br/>hp deskjet 960c * LPT1: * hp deskjet 960c * <br/>ctn154 * \\srv-ec01\ctn154 * HP LaserJet P2050 Series PCL6 * <br/>cnt_maint_1320 * \\srv-ec01\cnt_maint_1320 * hp LaserJet 1320 PCL 6 * <br/>\\srv-ec01\cnt_maint_1320 * IP_192.168.5.157 * hp LaserJet 1320 PCL 6 * Maintenance office<br/>\\srv-ec01\ctn150 * IP_192.168.5.150 * HP LaserJet 2300 Series PCL 6 * East Canton<br/>\\srv-ec01\ec156 * IP_192.168.5.156 * Xerox WorkCentre 5225 * <br/><br/>8/9/2012 8:03:58 PM - Logged in user:  jadams<br/>------------------------------------------------------<br/>Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * <br/>Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * <br/>HP LaserJet P2015 Series PCL 6 * USB001 * HP LaserJet P2015 Series PCL 6 * <br/>HP LaserJet 6P * LPT1: * HP LaserJet 6P * <br/>HP LaserJet 2100 PCL6 * LPT1: * HP LaserJet 2100 PCL6 * <br/>hp deskjet 960c * LPT1: * hp deskjet 960c * <br/>ctn154 * \\srv-ec01\ctn154 * HP LaserJet P2050 Series PCL6 * <br/>cnt_maint_1320 * \\srv-ec01\cnt_maint_1320 * hp LaserJet 1320 PCL 6 * <br/>\\srv-ec01\LANIER LD325 * IP_192.168.5.156 * LANIER MP 2510/LD325 PCL 6 * East Canton<br/><br/>3/26/2012 12:04:13 AM - Logged in user:  jrice<br/>------------------------------------------------------<br/>Microsoft Office Document Image Writer<br/>HP LaserJet P2015 Series PCL 6<br/>HP LaserJet 6P<br/>HP LaserJet 2100 PCL6<br/>hp deskjet 960c<br/>ctn154<br/>cnt_maint_1320<br/>\\srv-ec01\LANIER LD325<br/><br/>7/13/2012 12:04:33 AM - Logged in user:  rthomas<br/>------------------------------------------------------<br/>Microsoft Office Document Image Writer<br/>HP LaserJet P2015 Series PCL 6<br/>HP LaserJet 6P<br/>HP LaserJet 2100 PCL6<br/>hp deskjet 960c<br/>ctn154<br/>cnt_maint_1320<br/>\\srv-ec01\cnt_maint_1320<br/>\\srv-ec01\HP LaserJet 2100 Series PCL 6 (Copy 1)<br/>\\srv-ec01\LANIER LD325<br/><br/>4/22/2012 12:04:16 PM - Logged in user:  twalker<br/>------------------------------------------------------<br/>Microsoft Office Document Image Writer<br/>HP LaserJet P2015 Series PCL 6<br/>HP LaserJet 6P<br/>HP LaserJet 2100 PCL6<br/>hp deskjet 960c<br/>ctn154<br/>cnt_maint_1320<br/>\\srv-ec01\ctn150<br/>\\srv-ec01\ec156<br/><br/>

Open in new window


I tried using DISTINCT in these places:

FROM (SELECT DISTINCT a.stype)
> results in only one printer being listed

FROM (SELECT DISTINCT 1 x, '* USB' as stype UNION ALL SELECT DISTINCT... etc
> put before all SELECTs, no change in output

(SELECT DISTINCT @i:=0,@l:='',@d)v)y
> no change in output

So at this point I'm wondering if there might be a way to filter out the duplicates?  I'm guessing it would require expanding the a, b, c statements using IF..... but I'll be completely honest and admit it's beyond me at this point.

Thanks again for all of your help!!!  I feel very fortunate to have such a guru helping me!!!

John
0
 
rescoproductsAuthor Commented:
lwadwell,

I figured out how to take care of the LABEL.NAME issue, so at this point there's "just" the matter of excluding duplicate printer names in each field.  

Here's the working code to solve 1) in my previous post:

SELECT r.NAME as 'Machine', r.USER as 'User',
SUBSTRING(r.LOCATION, 1, length(r.LOCATION) - 10) as 'Location',
GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT M.NAME, M.USER, L.NAME as LOCATION,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN MACHINE M on (M.ID = MCI.ID)
           JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
           JOIN LABEL L ON (ML.LABEL_ID = L.ID)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')a, 
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')b, 
                            (SELECT 1 x, '* USB' as stype UNION ALL SELECT 1, '* DOT' UNION ALL SELECT 1, '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt'
      AND L.NAME rlike 'computers' 
      AND M.NAME not rlike 'dgreen|gwhite|wbrown')r
WHERE r.printer <> ''
  AND UPPER(r.printer) NOT RLIKE 'FAX'
GROUP BY r.NAME
ORDER by Location, Machine

Open in new window


Thanks again for all of your help!!!

John
0
 
rescoproductsAuthor Commented:
lwadwell,

I cannot say thank you enough for all of your time and help with this! ...particularly with the multiple examples you provided that I will be studying for some time to come.  For the record, (1) the first two variations you provided worked perfectly after some table/column name tweaking (the KACE database is apparently case sensitive) and the output is absolutely perfect.  (2) I'll comment on the third varation below, as well as (3) ask for one last favor (if you have time and don't mind).
_________________________________________________

(1) Just in case these may be of use to anyone else, here's the KACE K1000 working versions:

SELECT R.NAME as 'Machine', R.USER as 'User', R.LOCATION as 'Location',
       GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT DISTINCT M.NAME, M.USER, SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as Location,
             TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN MACHINE M ON (M.ID = MCI.ID)
           JOIN MACHINE_LABEL_JT ML ON (ML.MACHINE_ID = M.ID)
           JOIN LABEL L ON (L.ID = ML.LABEL_ID)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
      AND L.NAME RLIKE 'computers' 
      AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown')R
WHERE R.printer <> ''
  AND UPPER(R.printer) NOT RLIKE 'FAX'
GROUP BY R.NAME, R.USER, R.LOCATION
ORDER by Location, Machine

Open in new window



SELECT R.NAME as 'Machine', R.USER as 'User', R.LOCATION as 'Location',
       GROUP_CONCAT(DISTINCT printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT M.NAME, M.USER, SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as Location,
             TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN MACHINE M ON (M.ID = MCI.ID)
           JOIN MACHINE_LABEL_JT ML ON (ML.MACHINE_ID = M.ID)
           JOIN LABEL L ON (L.ID = ML.LABEL_ID)
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
      AND L.NAME RLIKE 'computers' 
      AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown')R
WHERE R.printer <> ''
  AND UPPER(R.printer) NOT RLIKE 'FAX'
GROUP BY R.NAME, R.USER, R.LOCATION
ORDER by Location, Machine

Open in new window


And here's an example of the resulting line of output for the 'Local Printer' field (same with either query):

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

Open in new window

_________________________________________________

(2) I really liked your third variation, as its flow was more similar to what I'm used to seeing/writing and I found it a little easier to follow.  Unfortunately, there were a number of errors when I tried to run it and I'm not quite sure how to get it working - not that it's critical since the other two work fine, but from a learning standpoint it would be great.  Honestly, though, if your time is limited, I'd much rather have help with (3) below, since that will expand my knowledge more than anything.

Anyways, here's the code with a couple of case tweaks and the errors:

SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
       GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT DISTINCT MCI.ID, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
      AND L.NAME RLIKE 'computers' 
      AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown')R
      -- Now JOIN in the other tables
      JOIN MACHINE M on (M.ID = MCI.ID) -- changed r.id to mci.id
      JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
      JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE R.printer <> ''
  AND UPPER(R.printer) NOT RLIKE 'FAX'
GROUP BY R.NAME, R.USER, SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10)
ORDER by Location, Machine

Open in new window



1) Ran query via MySQL Query Browser

The query could not be executed
ErrorNr.1054
Unknown column 'l.name' in 'where clause'

Removed line:
AND l.name RLIKE 'computers'


2) Ran query again

The query could not be executed
ErrorNr.1054
Unknown column 'm.name' in 'where clause'

Removed line:
AND m.name NOT RLIKE 'dgreen|gwhite|wbrown'


3) Ran query again

The query could not be executed
ErrorNr.1054
Unknown column 'r.name' in 'group statement'

Removed 'r.name' from GROUP BY statement


4) Ran query again

The query could not be executed
ErrorNr.1054
Unknown column 'r.user' in 'group statement'

Removed 'r.user' from GROUP BY statement


5) Query completed without errors, but results extremely truncated, duplicates among machines, location completely off, printers seem to be a concat of *all* local printers (not surprising with all of the WHERE statements removed, though).


6) Tried changing one JOIN (line 18 - changed r.id to mci.id), but same error as 1).  Not sure what to do from here.
_________________________________________________

(3) OK, if you have time or can point me to a good resource (or resources), I would really like to understand how this section of code is constructed and how it works.  I'm honestly at a loss with this section (beyond the standard MySQL commands like UNION, ORDER BY, etc).  I reference dev.mysql.com a lot for help with function syntax (etc), but am not coming up with much here since I honestly don't know what to search for.  Just a little background, I studied C++ on my own about 10 years ago and can cobble together and tweak (non-guru) code examples in vbscript, basic and MySQL - in other words, I'm not a programmer by any stretch, but I can usually break simpler stuff apart and understand a couple of concepts.

FROM MACHINE_CUSTOM_INVENTORY MCI
     JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
           FROM (SELECT a.stype
                 FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                      (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                      (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                 ORDER BY a.stype)u,
                (SELECT @i:=0,@l:='',@d)v)y
WHERE MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')r

Open in new window


1) Where are "variables" like stype, @i, @l, idx, @d, a, b, c, v & y coming from?  Are these native operators in MySQL or are you using them without declaring them?  (I tried Googling and browsing around dev.mysql.com, but didn't come up with much...)


2) I'd really like to understand what these two lines in particular are doing:

JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype

Open in new window


(SELECT @i:=0,@l:='',@d)v)y

Open in new window



3) Why are the SELECT '* USB' lines identical (aside from the aliases as a, b & c) and why does it take three when only a.stype is used elsewhere in the query?  Also, why is this entire section aliased as "u" when it's never referenced again (and same question on the "v" and "y" aliases in the following line)?

(SELECT a.stype
FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
     (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
     (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
ORDER BY a.stype)u,
(SELECT @i:=0,@l:='',@d)v)y

Open in new window

_________________________________________________

Thanks yet again for *ALL* of your help and I will defintely award all points to you, no worries about that!  I feel truly fortunate to have such an expert help me solve this complex problem and help me understand how!  Thank you!!!

John
0
 
lwadwellCommented:
Firstly ... my bad this:
SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
       GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT DISTINCT MCI.ID, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
      AND L.NAME RLIKE 'computers' 
      AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown')R
      -- Now JOIN in the other tables
      JOIN MACHINE M on (M.ID = MCI.ID) -- changed r.id to mci.id
      JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
      JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE R.printer <> ''
  AND UPPER(R.printer) NOT RLIKE 'FAX'
GROUP BY R.NAME, R.USER, SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10)
ORDER by Location, Machine

Open in new window


needed the some of the inner WHERE moved to the outside where, after the joins where being done ... and fix the GROUP BY to match the SELECT values (and synonyms):
SELECT M.NAME as 'Machine', M.USER as 'User', SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as 'Location',
       GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT DISTINCT MCI.ID, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
      FROM MACHINE_CUSTOM_INVENTORY MCI
           JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
                 FROM (SELECT a.stype
                       FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
                            (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
                       ORDER BY a.stype)u,
                      (SELECT @i:=0,@l:='',@d)v)y
      WHERE MCI.SOFTWARE_ID = 6560
      AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt')R
      -- Now JOIN in the other tables
      JOIN MACHINE M on (M.ID = r.ID)
      JOIN MACHINE_LABEL_JT ML on (ML.MACHINE_ID = M.ID)
      JOIN LABEL L on (L.ID = ML.LABEL_ID)
WHERE R.printer <> ''
  AND UPPER(R.printer) NOT RLIKE 'FAX'
  AND L.NAME RLIKE 'computers' 
  AND M.NAME NOT RLIKE 'dgreen|gwhite|wbrown'
GROUP BY M.NAME, M.USER, SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10)
ORDER by Location, Machine

Open in new window


Hopefully that is better.

I will try and answer your other questions in another reply.
0
 
lwadwellCommented:
OK ... how does the 'fudge' work...

Let's start with the easy one:
>> Where are "variables" like @i, @l ... coming from?
A> (SELECT @i:=0,@l:='',@d)v
This is an in-line view that defines the variables (actually I didn't use @d and it could be removed).  This is a MySQL feature.  It creates the variables for use elsewhere in the query.
The 'v' is the alias for the in-line view ... EVERY view in MySQL needs an alias (so the columns in that view can be referenced).  This is also why there is a, b, c, u, y & r

>> SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
A> It's like code ... think of the SELECT being a WHILE loop reading records off the FROM clause. e.g. (using an arbitrary pseudo code style) ... I am sure you can figure it out so I will not spoil the ending for you:
WHILE (input exists) {
  print stype;
  if stype=@l then @i:=@i+1 else @i:=1 endif;
  print @i;
  @l:=stype;
  print @l;
}

Open in new window

This is ALSO why the ORDER by exists to ensure the input is read in stype order.

>> Why are the SELECT '* USB' lines identical ...
A> I am actually doing a deliberate naughty thing here ... did you notice that there was no joins being done here, e.g. "... ON cola = colb ..." or "... WHERE cola = colb ..."?  This is because I was performing a CARTESIAN PRODUCT.  This is where every row in one table (or in-line view in this case) is joined with EVERY row in the other table (in-line view).  These are normally to be avoided.
I could replace that whole inline view with alias y with:
(SELECT 1 as idx, '* USB' as stype UNION ALL
 SELECT 2 as idx, '* USB' as stype UNION ALL
 SELECT 3 as idx, '* USB' as stype UNION ALL
 SELECT 4 as idx, '* USB' as stype UNION ALL
 SELECT 5 as idx, '* USB' as stype UNION ALL
 SELECT 6 as idx, '* USB' as stype UNION ALL
 SELECT 7 as idx, '* USB' as stype UNION ALL
 SELECT 8 as idx, '* USB' as stype UNION ALL
 SELECT 9 as idx, '* USB' as stype UNION ALL
 SELECT 1 as idx, '* DOT' as stype UNION ALL
 SELECT 2 as idx, '* DOT' as stype UNION ALL
 SELECT 3 as idx, '* DOT' as stype UNION ALL
 SELECT 4 as idx, '* DOT' as stype UNION ALL
 SELECT 5 as idx, '* DOT' as stype UNION ALL
 SELECT 6 as idx, '* DOT' as stype UNION ALL
 SELECT 7 as idx, '* DOT' as stype UNION ALL
 SELECT 8 as idx, '* DOT' as stype UNION ALL
 SELECT 9 as idx, '* DOT' as stype UNION ALL
 SELECT 1 as idx, '* LPT' as stype UNION ALL
 SELECT 2 as idx, '* LPT' as stype UNION ALL
 SELECT 3 as idx, '* LPT' as stype UNION ALL
 SELECT 4 as idx, '* LPT' as stype UNION ALL
 SELECT 5 as idx, '* LPT' as stype UNION ALL
 SELECT 6 as idx, '* LPT' as stype UNION ALL
 SELECT 7 as idx, '* LPT' as stype UNION ALL
 SELECT 8 as idx, '* LPT' as stype UNION ALL
 SELECT 9 as idx, '* LPT' as stype )y

Open in new window

which is what it generates ... take the original in-line view and run it by itself and take the one above and run it two ... the results should be the similar. i.e.
SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
FROM (SELECT a.stype
      FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
           (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
           (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
      ORDER BY a.stype)u,
     (SELECT @i:=0,@l:='',@d)v

Open in new window


I just forced the creation of multiple rows to be joined into the SUBSTRING_INDEX as the search string and the n'th occurrence value.  I wasn't sure how many would be required ... I though 9 would be safe for each.

>> why only a.stype
A> It was all I needed, run this and look at the result:
SELECT *
FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a, 
     (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b, 
     (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
ORDER BY a.stype

Open in new window


>> Why are some aliases not being used
A> Laziness

Did I miss anything?
0
 
rescoproductsAuthor Commented:
lwadwell,

Thanks again for everything!  The revised third variation works fine now and I truly appreciate you taking the time to explain the "fudge" - although with my db access it's the only way I can do something like this, so it's not really a "fudge" in my book.

Please accept full points with my sincere gratitude and appreciation, and keep up the outstanding work!!!

John
0
All Courses

From novice to tech pro — start learning today.