MS SQL: complex query that has multiple queries in one

Pity you just use "select *" instead of listing out the columns you actually want/need. The only column names I know that exists in table "system" are [CLASS], [CLASSNAME], [KEYNAME], [KEY]

That being said a more conventional way to achieve what I think you want would be to use "conditional aggregates" like this

select
       [CLASS] AS EXTENSION
       
     , max(case when KEYNAME = '#APPLICATION' then [CLASS] end)     as Application
     , max(case when KEYNAME = '#APPLICATION' then [CLASSNAME] end) as ApplicationType
     , max(case when KEYNAME = '#APPLICATION' then [KEY] end)       as ApplicationKey
     
     , max(case when KEYNAME = '#VBOX'        then [CLASS] end)     as Vbox 
     , max(case when KEYNAME = '#VBOX'        then [CLASSNAME] end) as VboxType
     , max(case when KEYNAME = '#VBOX'        then [KEY] end)       as VboxKey
from system
where [keyname] in ('#APPLICATION','#VBOX')
group by
       [CLASS] ]

Select allOpen in new window

Note: With this approach is you will get rows even if there are classes without both '#APPLICATION' and '#VBOX'.
i.e. a row will display if there is an application without a matching vbox or vice versa.

You can repeat the column clusters for each extra value found in [KEYNAME]  
e.g. if there is also a value to be found in [KEYNAME] that is '#xyz', then:

select
       [CLASS] AS EXTENSION
       
     , max(case when KEYNAME = '#APPLICATION' then [CLASS] end)     as Application
     , max(case when KEYNAME = '#APPLICATION' then [CLASSNAME] end) as ApplicationType
     , max(case when KEYNAME = '#APPLICATION' then [KEY] end)       as ApplicationKey
     
     , max(case when KEYNAME = '#VBOX'        then [CLASS] end)     as Vbox 
     , max(case when KEYNAME = '#VBOX'        then [CLASSNAME] end) as VboxType
     , max(case when KEYNAME = '#VBOX'        then [KEY] end)       as VboxKey
     
     , max(case when KEYNAME = '#xyz'        then [CLASS] end)     as xyz 
     , max(case when KEYNAME = '#xyz'        then [CLASSNAME] end) as xyzType
     , max(case when KEYNAME = '#xyz'        then [KEY] end)       as xyzKey
from system
where [keyname] in ('#APPLICATION','#VBOX','#xyz')
group by
       [CLASS] 

Select allOpen in new window

PortletPaul, thank you for your input.
The "SYSTEM" table has many more fields then just the ones I listed.

I tested your second code submission "Vbox   VboxType   VboxKey   xyz   xyzType   xyzKey" columns return null.
I'm assuming you are presenting me another way to combine my three queries into one. With that I might be able to proceed.

I will play around with this and respond later.

In your question you show that for  [class] 11011121600 that there is both an application and a vbox. So I
would not expect VBOX related columns to be only nulls, but certainly would expect XYZ columns to be null as that is just an example I plucked from thin air.

It would be helpful to have some "sample data" and also the result you would expect from that data. 

Are you saying your second code sample was regarding my forth query. Hmm.

IF the sample data was this:

     CLASS      CLASSNAME     KEYNAME                      KEY                   
 ------------- ----------- -------------- -------------------------------------- 
  11011121600   VMX         #APPLICATION   AF71B74D301842C                       
  11011121600   VMX         #VBOX          BC56379D-B99C-4058-A563-E0CE3D0C1480  
  A1011121600   VMX         #APPLICATION   AF71B74D301842C                       
  B1011121600   VMX         #VBOX          BC56379D-B99C-4058-A563-E0CE3D0C1480  
  11011121600   VMX         #xyz           AF71B74D301842C                       

Select allOpen in new window

then:

CREATE TABLE system(
   CLASS     VARCHAR(11) NOT NULL
  ,CLASSNAME VARCHAR(3) NOT NULL
  ,KEYNAME   VARCHAR(12) NOT NULL
  ,[KEY]       VARCHAR(36) NOT NULL
);
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('11011121600','VMX','#APPLICATION','AF71B74D301842C');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('11011121600','VMX','#VBOX','BC56379D-B99C-4058-A563-E0CE3D0C1480');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('A1011121600','VMX','#APPLICATION','AF71B74D301842C');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('B1011121600','VMX','#VBOX','BC56379D-B99C-4058-A563-E0CE3D0C1480');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('11011121600','VMX','#xyz','AF71B74D301842C');

Select allOpen in new window

and the query I provided earlier:

select
       [CLASS] AS EXTENSION
       
     , max(case when KEYNAME = '#APPLICATION' then [CLASS] end)     as Application
     , max(case when KEYNAME = '#APPLICATION' then [CLASSNAME] end) as ApplicationType
     , max(case when KEYNAME = '#APPLICATION' then [KEY] end)       as ApplicationKey
     
     , max(case when KEYNAME = '#VBOX'        then [CLASS] end)     as Vbox 
     , max(case when KEYNAME = '#VBOX'        then [CLASSNAME] end) as VboxType
     , max(case when KEYNAME = '#VBOX'        then [KEY] end)       as VboxKey
     
     , max(case when KEYNAME = '#xyz'        then [CLASS] end)     as xyz 
     , max(case when KEYNAME = '#xyz'        then [CLASSNAME] end) as xyzType
     , max(case when KEYNAME = '#xyz'        then [KEY] end)       as xyzKey
from [system]
where [keyname] in ('#APPLICATION','#VBOX','#xyz')
group by
       [CLASS] 

Select allOpen in new window

will produce this result:

   EXTENSION    Application   ApplicationType   ApplicationKey       Vbox       VboxType                 VboxKey                      xyz       xyzType       xyzKey       
 ------------- ------------- ----------------- ----------------- ------------- ---------- -------------------------------------- ------------- --------- ----------------- 
  11011121600   11011121600   VMX               AF71B74D301842C   11011121600   VMX        BC56379D-B99C-4058-A563-E0CE3D0C1480   11011121600   VMX       AF71B74D301842C  
  A1011121600   A1011121600   VMX               AF71B74D301842C                                                                                                            
  B1011121600                                                     B1011121600   VMX        BC56379D-B99C-4058-A563-E0CE3D0C1480                                            

Select allOpen in new window

Notice I have deliberately included data that does not fill out every cell in the result, but IF you have 3 original rows that share the  same [Class] and those 3 rows have  [keyname] in ('#APPLICATION','#VBOX','#xyz') THEN the query will combine those 3 rows into one row of output.

This data and query is available here as a working demonstration.

I hope by doing the above you will see that advantage of providing sample data (and including in that sample exceptions you need tested) plus the advantage of being able to see the result. To this point I have largely guessed what you you are really looking for, but perhaps now you could supply sample data and the expected result?

I thinking I'm doing this wrong. Here is my todo:

The "SYSTEM" table columns that contain what I need are CLASS, CLASSNAME, KEYNAME, KEY.
Each of my search's result appear in a single row. Like row 1:
    CLASSNAME   CLASS   KEYNAME   KEY
    VBOX   11011131114   #APPLICATION   CA8B0363E442488
    VBOX   11011131114   #VBOX   E6445954-0910-4E97-A620-02175EAD9610

The extension number is in CLASS column but to find them I need to search KEYNAME for "#APPLICATION" which holds the numeric id along with the extension.

Same as for the "VBOX" (also in the KEYNAME column)

From there it is necessary to back to the SYSTEM table search for the #VBOX with the numeric id to search for CLASS = "E6445954-0910-4E97-A620-02175EAD9610" and KEYNAME = "NAME" to get the KEY result.

Do this for each of the "#VBOX" results.

Then do the same for the "#APPLICATION" results

Then place "EXTENSION", "#APPLICATION.NAME", "#VBOX.NAME" in the final result.

All this (for me) with very limited knowledge of SQL command understanding.

It would be ever so much easier if you supplied a realistic sample of data that includes the "numeric id" you refer to.
Believe me after years of answering questions here, the fastest & most satisfactory way of getting a working solution is to provide:

1. sample data (with ALL needed columns) and that cover all needed test cases
   (nb: the sample does not need to be large)
2. the expected result, this must be derivable from the sample of data
   (& it may be necessary to prepare this expected result manually)

Honestly, there is a vital piece of information missing so far and that is example of the "numeric ids" that you refer to.
This is the bit you want solved but haven't revealed details of:


PLEASE display some sample of data that represents the full problem

This is a sample db.
Scope has changed I'm only to preset the #VBOX's name.
1) Find all the #VBOX (in KEYNAME column) to get the box numeric ID (in KEY column)
2) Find all the #VBOX's names.
3) Display the names only in ascending order

Asked to forget about the #APPICATIONS date for now.
4ExpertsExchangeHelp.db

I cannot use that file, sorry.
Truly all I need is sample which you can paste into a comment or as an Excel file or csv file.

It is just a text file could rename. No matter here is a .cvs version of what I sent you.
4ExpertsExchangeHelp.csv
4ExpertsExchangeHelp.xls
Not sure which Excel format you want I hope the "xls" is ok.
Thank you for your help PortletPaul

PortletPaul, thank you for hanging in there and getting a solution.
I had to add "DISTINCT" in order to prevent duplicates when ran against the SQL production table. Have not stepped through to see why so many repeats but no matter.

I'm going to run this through my database on the SQL some and hand check to see if there are any other issues.
Believe I will be able to respond in the next day or so to mark this solved.

Thanks again.

If you needed to use SELECT DISTINCT then there probably are problems with your data quality.

It appears to me that the "system" table represents some form of object oriented approach that allows for "custom fields" (or "attributes") to be added to an "object". These approaches are the opposite of more traditional 3rd normal form table design use in relational databases, and consequently such object oriented tables are really poorly suited to SQL queries. Despite this they are used because they are "easy to use" (in the user interface) and hide the underlying ugliness from users.

BUT, a name should only exist once in any data model. If they are repeated then this raises the possibility that one "object instance" might get different vales for "name" depending on which row in the table has been selected. That is not good.

I do hope you don't have such a problem in your data as it could be hard to debug and solve.

This query MIGHT solve the original question, or it just might be useful because it "pivots" the data so that each "keyname" becomes a column, and the number of rows is much smaller. In fact, in the sample data I get just 4 rows as the result, like this:

| CATEGORIES  |    CLASSNAME    |                CLASS                 | BLINDTRANSFER | FORWARDKEY | INTERRUPTIBLE |    LAST_UPDATE     | MAXPLAYTIME |          NAME           | PBXSWITCHING | PLAYLASTPOS | QTY_INVALIDKEYS | QTY_NOINPUT | RANDOMPLAYLIST | STATISTIC | TIMEOUT | TYPE | VAR_RESULT |
|-------------|-----------------|--------------------------------------|---------------|------------|---------------|--------------------|-------------|-------------------------|--------------|-------------|-----------------|-------------|----------------|-----------|---------|------|------------|
| $VOICEBOXES | 235FAAEF975046E | 54EB80BD-1DF9-41CC-92D2-71F4E4139BC9 |               |            | X             | 23/4/2021 13:11:55 |           0 | CLERKS Main menu        |              | _           |               3 |           3 | _              | X         |       5 | MENU |            |
| $VOICEBOXES | 246856681A1B41D | 48E98324-A3ED-4B64-B721-AB22F12E8EED |               |            |               | 23/4/2021 13:41:33 |             | ENGINEERING NEW MENU    |              | _           |               2 |           2 | _              | X         |       5 | MENU |            |
| $VOICEBOXES | 246856681A1B41D | B467D086-86EB-438E-9D58-814DA9783508 |               |            | X             | 23/4/2021 13:41:33 |           0 | BDRS MAIN MENU          |              | _           |               3 |           3 | _              | X         |       2 | MENU |            |
| $VOICEBOXES | 8DC0C20DFC194CD | 8AEBAC98-477B-48A6-B92E-97E3CCCFE494 |               |            | X             | 24/7/2019 10:37:1  |           0 | HOLIDAY Closed Greeting |              | _           |                 |             | _              | X         |         | PLAY |            |

Select allOpen in new window

The query I used to produce this involves some "dynamic sql" (sql that produces sql code and then executes it) which is needed to get the keynames as column names:

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(s.keyname)
            FROM system s
            WHERE NOT keyname LIKE '#%'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT CATEGORIES,CLASSNAME,CLASS, ' + @cols + ' FROM
            (
                SELECT
                      s.CATEGORIES,s.CLASSNAME,s.CLASS,s.KEYNAME,s.[KEY]
                 FROM system s
                 inner join (
                     select
                           class as grouping
                         , max(case when keyname = ''#APPLICATION'' then [key] end) as gclassname
                         , max(case when keyname = ''#VBOX'' then [key] end) as gclass
                     from system
                     where keyname in (''#APPLICATION'',''#VBOX'')
                     group by 
                          class
                          ) g on s.classname = g.gclassname and s.class = g.gclass
            ) sourcedata
            pivot
            (
                 max([key])
                FOR [keyname] IN (' + @cols + ')
            ) p '

--select @query -- use select to inspect the generated sql
execute(@query) -- once satisfied that sql is OK, use execute

Select allOpen in new window

note at the bottom 2 rows, you can "select" the generated SQL to inspect it (which I do to debug before executing)
The generated sql query looks like this:

SELECT CATEGORIES,CLASSNAME,CLASS, [BLINDTRANSFER],[FORWARDKEY],[INTERRUPTIBLE],[LAST_UPDATE],[MAXPLAYTIME],[NAME],[PBXSWITCHING],[PLAYLASTPOS],[QTY_INVALIDKEYS],[QTY_NOINPUT],[RANDOMPLAYLIST],[STATISTIC],[TIMEOUT],[TYPE],[VAR_RESULT] FROM
(
    SELECT
          s.CATEGORIES,s.CLASSNAME,s.CLASS,s.KEYNAME,s.[KEY]
     FROM system s
     inner join (
         select
               class as grouping
             , max(case when keyname = '#APPLICATION' then [key] end) as gclassname
             , max(case when keyname = '#VBOX' then [key] end) as gclass
         from system
         where keyname in ('#APPLICATION','#VBOX')
         group by 
              class
              ) g on s.classname = g.gclassname and s.class = g.gclass
) sourcedata
pivot
(
     max([key])
    FOR [keyname] IN ([BLINDTRANSFER],[FORWARDKEY],[INTERRUPTIBLE],[LAST_UPDATE],[MAXPLAYTIME],[NAME],[PBXSWITCHING],[PLAYLASTPOS],[QTY_INVALIDKEYS],[QTY_NOINPUT],[RANDOMPLAYLIST],[STATISTIC],[TIMEOUT],[TYPE],[VAR_RESULT])
) p 

Select allOpen in new window

all this may be viewed here at dbfiddle.uk

PortletPaul,
Thank you for your input.
This exercise has uncovered a cleanup of the database is necessary. Testing shows your solution works.
I'm got some work to do.

One thing I have not taken into account in any of my queries is the [categories] column, not sure what role it plays but perhaps duplicated names were due to differences in that column?

Anyway: Best of luck with whatever you need to do.