[Solved] How to combine two SQLite Queries into one returned result

[benners]

It seems  I'm always asking SQL questions in December,  must be a tradition. Here is the latest

I have a database that is queried to return infomation about software installers and users. I can achieve what I want by calling two queries using _SQLite_QuerySingleRow, and adding the second query to the first returned array using _ArrayAdd. What I want to do is try to do it with one query.

I don't have any code as I try the queries in SQLitexpert before to obtain the results and layout I require. The one below returns the information I want but the info is duplicated for every name that uses the program. I know this is down to my code

SELECT 
       [main].[platform].[id], 
       [main].[installer].[path], 
       [main].[package].[type], 
       [main].[installer].[switches],        
       [main].[minwinver].[version], 
       [main].[category].[class], 
       [main].[installer].[install_order], 
       [main].[installer].[id] AS [id1], 
       [main].[installer].[display_name], 
       [main].[installer].[display_version], 
       [main].[installer].[display_description], 
       [main].[user].[username]
FROM   [main].[installer]
       INNER JOIN [main].[category] ON [main].[category].[id] = [main].[installer].[category_id]
       INNER JOIN [main].[installer_user] ON [main].[installer].[id] = [main].[installer_user].[installer_id]
       INNER JOIN [main].[user] ON [main].[user].[id] = [main].[installer_user].[user_id]
       INNER JOIN [main].[platform] ON [main].[platform].[id] = [main].[installer].[platform_id]
       INNER JOIN [main].[package] ON [main].[package].[id] = [main].[installer].[package_id]
       INNER JOIN [main].[minwinver] ON [main].[minwinver].[id] = [main].[installer].[minwinver_id]
WHERE   [main].[installer].[display_name] = 'SciTE4AutoIt3'
         AND [main].[installer].[id] = 5;

What I have done is split the sql calls. This one gets the software

SELECT 
       [main].[platform].[id], 
       [main].[installer].[path], 
       [main].[package].[type], 
       [main].[installer].[switches],        
       [main].[minwinver].[version], 
       [main].[category].[class], 
       [main].[installer].[install_order], 
       [main].[installer].[id] AS [id1], 
       [main].[installer].[display_name], 
       [main].[installer].[display_version], 
       [main].[installer].[display_description]
FROM   [main].[installer]
       INNER JOIN [main].[category] ON [main].[category].[id] = [main].[installer].[category_id]
       INNER JOIN [main].[platform] ON [main].[platform].[id] = [main].[installer].[platform_id]
       INNER JOIN [main].[package] ON [main].[package].[id] = [main].[installer].[package_id]
       INNER JOIN [main].[minwinver] ON [main].[minwinver].[id] = [main].[installer].[minwinver_id]
WHERE   [main].[installer].[display_name] = 'SciTE4AutoIt3'
         AND [main].[installer].[id] = 5;

This one gets the users. The users will be added to a combo so it doesn't matter if it's an array, but I would prefer a delimited string return.

SELECT group_concat([main].[user].[username])
       
FROM   [main].[installer]
       INNER JOIN [main].[installer_user] ON [main].[installer].[id] = [main].[installer_user].[installer_id]
       INNER JOIN [main].[user] ON [main].[user].[id] = [main].[installer_user].[user_id]
WHERE  [main].[installer].[id] = 5
ORDER  BY [main].[installer_user].[user_id];

Is it possible to return the result in the second example along with the third in one array using SQLite. I have been googling and trying different things that are obviously incorrect. I have attached the database in question.

Thanks

Installers.dbc

Edited December 26, 2021 by benners

[TheXman]

2 hours ago, benners said:

Is it possible to return the result in the second example along with the third in one array using SQLite.

I don't know what you meant by "in one array using sqlite".  If you meant in a single SQLITE function call, then there are several ways.  Below, is an example of one of the ways you could do it.

2 hours ago, benners said:

It seems  I'm always asking SQL questions in December,  must be a tradition.

Since I'm the one that replied to your previous SQL-related topic, almost a year ago to the day, I'll keep the tradition going by replying to this one too. 😉

 

SELECT plat.id, 
       i.path, 
       pkg.type, 
       i.switches,        
       ver.version, 
       c.class, 
       i.install_order, 
       i.id AS installer_id, 
       i.display_name, 
       i.display_version, 
       i.display_description, 
       group_concat(u.username) AS user_list
FROM   installer i
       INNER JOIN category c        ON c.id = i.category_id
       INNER JOIN installer_user iu ON i.id = iu.installer_id
       INNER JOIN user u            ON u.id = iu.user_id
       INNER JOIN platform plat     ON plat.id = i.platform_id
       INNER JOIN package pkg       ON pkg.id = i.package_id
       INNER JOIN minwinver ver     ON ver.id = i.minwinver_id
WHERE  i.display_name = 'SciTE4AutoIt3' AND i.id = 5
GROUP BY 
       plat.id, 
       i.path, 
       pkg.type, 
       i.switches,        
       ver.version, 
       c.class, 
       i.install_order, 
       i.id, 
       i.display_name, 
       i.display_version, 
       i.display_description;

 

Edited December 26, 2021 by TheXman

[benners]

Yeah, Sorry about the "one array". Half the info was left in my head. I am using AutoIT for the program and the _SQLite* functions for DB interaction. When using _SQLite_QuerySingleRow, the function returns an array. I use this array to populate controls in a loop.

Your code above works great, and I will be able to use it when it becomes a function. Seems like this project will never get finished. Thanks for your continued support. See you next year 😆

Edited December 26, 2021 by benners
spelling

[TheXman]

Having a graphical view of your tables and their relationships to one another can be very helpful, especially in the design phase but also when creating queries.  Since you're already at least a year into this project, the diagram below may not be as helpful as it could've been back when you were first getting started.  Nevertheless, it may still help you if/when you decide to add new tables, consolidate existing ones, or create additional relationships. 

See you next year.  

[benners]

Yeah that helps. The SQLite Expert program that jchd put me on to has an sql builder section. This has helped me loads, when building and to understand the returned queries. It shows the relationships betwen the table, albeit not as clearly as your picture. It is great for simple queries, which I then convert to AutoIt functions. For more complex ones, it's to the books or google to see if it is possible to do it, which it always has been.

There will be a lot more tables as each category will have one with switches to be used with the installer files. This program only creates and edits the db files, (see pic) I still need to create the program that reads the db.

If I could stop redesigning GUI's, adding functionality, changing font colours and stop trying to make code that's clean and short while still readable (instead of making it work then trimming) I may have finished it sooner. A lot of time is also lost on adding logging (I wrote my own UDF which took a while) and return codes etc, though this is not wasted time.

It seems like whatever I want to do, I have to discover how to do it. Thanks again for them pic, saved and will print off.

[TheXman]

I took a closer look at your DB and, in this particular case, determined that you really didn't need all of the additional GROUP BY fields in my previous reply.  I didn't look closely at the database before, just your queries,  The query below yields the same result.

SELECT 
    i.id AS installer_id, 
    i.display_name,
    plat.id AS platform_id, 
    plat.os_arch,
    i.path, 
    pkg.type, 
    i.switches,        
    mwv.version, 
    c.class, 
    i.install_order, 
    i.display_version, 
    i.display_description, 
    group_concat(u.username) AS user_list
FROM
    installer i
    INNER JOIN category c        ON c.id = i.category_id
    INNER JOIN installer_user iu ON iu.installer_id = i.id
    INNER JOIN user u            ON u.id = iu.user_id
    INNER JOIN platform plat     ON plat.id = i.platform_id
    INNER JOIN package pkg       ON pkg.id = i.package_id
    INNER JOIN minwinver mwv     ON mwv.id = i.minwinver_id
WHERE
    i.id = 5 AND i.display_name = 'SciTE4AutoIt3'
GROUP BY i.id;

 

Edited December 26, 2021 by TheXman

[benners]

Yeah. I noticed that too. I initially created the function without the Group By, I then got to thinking that you might have added them to ensure to return the results in a specific way. Even though I would expect the results to return in the sequence they are in the SELECT statement, and as I'm returning just one item (id) there shouldn't be any identical data to arrange. I added them back to the function for completeness. I'll update this now.

I have tried to normalise the DB as much as possible and marry the fields and values with AutoIt and it's controls (integers for combos to set current selection). The selection of apps is just a test to enable error checking\testing. There will be exact matches on the display_name as some apps have separate installers for 32 and 64 bits but the paths will be different as I append -x86, -x64, -Dual to the file names (if not already there) to enable easier identification.

Thanks for your diligence. I have a question about the aliases? Do you find it easier with code that shortens the table\columns names like (installer i), is there a performance increase with shorter terminology. For a beginner like myself, I do find it makes it harder to understand as I am not used to thinking that way. I always try to slim my AutoIt stuff down to the minimum fluff so I should also do the same with the SQL. I have updated the function with the edit you suggested,

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name ..........: _DBI_MainInstallGetValues
; Description ...:  Retrieves the stored values for the specified application and id
; Syntax ........: _DBI_MainInstallGetValues($s_AppName, $i_AppID[, $h_DB = -1])
; Parameters ....: $s_AppName - a string value. The name of the app to query
;                  $i_AppID   - an integer value. The database ID (used for multiple apps with same name different platform)
;                  $h_DB      - [optional] a handle value. Default is -1. The database to query
; Return values .: Success: A 1D array containing the query values
;                  Failure: string describing in english the error condition for the most recent sqlite3_* API call
; Author ........: Benners
; Modified ......:
; Remarks .......:
; Link ..........: Based on code from TheXman below
; https://www.autoitscript.com/forum/topic/207243-solved-how-to-combine-two-sqlite-queries-into-one-returned-result/?tab=comments#comment-1494299
; ===============================================================================================================================
Func _DBI_MainInstallGetValues($s_AppName, $i_AppID, $h_DB = -1)
    Local $s_SQL = _
            "SELECT " & _
            "[installer].[id], " & _
            "[installer].[path],  " & _
            "[package].[id] AS [pack.id], " & _
            "[installer].[switches], " & _
            "[platform].[id] AS [plat.id], " & _
            "[minwinver].[id] AS [minwin.id], " & _
            "[category].[class], " & _
            "[installer].[install_order], " & _
            "[installer].[display_name], " & _
            "[installer].[display_version], " & _
            "[installer].[display_description], " & _
            "GROUP_CONCAT ([user].[username], '|') AS [user_list] " & _
            "FROM   [installer] " & _
            "INNER JOIN [package] ON [package].[id] = [installer].[package_id] " & _
            "INNER JOIN [platform] ON [platform].[id] = [installer].[platform_id] " & _
            "INNER JOIN [minwinver] ON [minwinver].[id] = [installer].[minwinver_id] " & _
            "INNER JOIN [installer_user] ON [installer].[id] = [installer_user].[installer_id] " & _
            "INNER JOIN [user] ON [user].[id] = [installer_user].[user_id] " & _
            "INNER JOIN [category] ON [category].[id] = [installer].[category_id] " & _
            "WHERE  [installer].[id] = " & $i_AppID & " " & _
            "AND [installer].[display_name] = " & _SQLite_FastEscape($s_AppName) & _
            "GROUP BY " & _
            "[installer].[id];"

    Local $a_Row = 0
    Local $v_Ret = _SQLite_QuerySingleRow($h_DB, $s_SQL, $a_Row)
    If $v_Ret <> $SQLITE_OK Then Return SetError(@error, @extended, _SQLite_ErrMsg())

    ; add the available install order slots to the current one
    $a_Row[$DB_INSTALLORDER] = $a_Row[$DB_INSTALLORDER] & '|' & _DBI_InstallOrderGetAvailable()

    Return $a_Row
EndFunc   ;==>_DBI_MainInstallGetValues

 

[TheXman]

32 minutes ago, benners said:

Do you find it easier with code that shortens the table\columns names like (installer i), is there a performance increase with shorter terminology.

I don't think using aliases for table names (or field names) has much of an effect on performance, if any at all.  The use of aliases, in most cases, is just a personal preference (or a standard in some organizations).  For me, it's just a way to cut down on a LOT of unnecessary keystrokes.  😉  In any language, whether spoken or coding, there's usually more than one way to say the same thing.  How you say something depends more on you and your environment, training/education, and comfort level more than anything else.  😀

Edited December 27, 2021 by TheXman