Jump to content
gcue

stuck on a query to identify unique items

Recommended Posts

gcue

hello

I have several devices (flash drives, external drives) which have songs.  i am tracking all the songs residing on each device in a sql database which has only 2 columns - device_name and song_name.  I would like to identify the songs on each device and show the songs missing on the remaining devices.  Then I can choose which ones I want to copy over and which ones I do not. (some devices belong to my kids so dont want to synch all the songs)

i do a select distinct sql query to id each device.  i thought about exporting an array of the entire database to cycle through but am stuck on the logic

can anyone please help?

here is what ive started with

Local $array, $iRows, $iColumns, $iRval

    _SQLite_Open($sql_db)

    $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM aSongs;", $array, $iRows, $iColumns)
    If $iRval <> $SQLITE_OK Then
        MsgBox($msg_error, @ScriptName, "Unable to generate array from SQL db.")
        Return
    EndIf


    Local $hQuery, $aRow

    _SQLite_Open($sql_db)

    _SQLite_Query(-1, "SELECT DISTINCT Device_Label FROM aSongs", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
;~      GUICtrlCreateListViewItem("|", $compare_listview)
    WEnd

    _SQLite_QueryFinalize($hQuery)

    _SQLite_Close()

thanks in advance.

Share this post


Link to post
Share on other sites
TheXman

@gcue

Below is an example of a few basic queries, that you can modify as needed, that can return some of result sets that you detailed above.  There are numerous ways that it can be done.  The example, below, is not meant to be exhaustive nor is it meant to be used as-is.  It is merely provided to give you an idea of one way that it can be done and to help you move forward.

Click below to reveal the example

Spoiler
#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "C:\Program Files\SQLite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 5');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 6');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")

        ;Display songs table
        ConsoleWrite("Temp Song Table" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT * FROM songs;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;Display list of unique song names in the songs table
        ConsoleWrite(@CRLF & "List of unique song names" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT DISTINCT name FROM songs ORDER BY name;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;List of songs on Device A
        ConsoleWrite(@CRLF & "List of all songs on Device A" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT device, name " & _
            "FROM songs " & _
            "WHERE device = 'Device A' " & _
            "ORDER BY name;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;List of songs missing from Device A
        ConsoleWrite(@CRLF & "List of all songs not on Device A and which devices have it" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT name, device " & _
            "FROM songs " & _
            "WHERE name NOT IN (SELECT name from songs WHERE device = 'Device A') " & _
            "ORDER BY device, name;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()
EndFunc

 

 

Share this post


Link to post
Share on other sites
jchd

You can also use the following query sketch:

select ... from tableA except select ... from tableB

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
gcue

TheXman

Thank you for the example however will that work if there are multiple devices? If so, would I have to write a new set of code every time a device is added? 

Jchd, 

Only have 1 table with all the info that's why I have the device_name column. 

 

Share this post


Link to post
Share on other sites
TheXman
48 minutes ago, gcue said:

will that work if there are multiple devices? If so, would I have to write a new set of code every time a device is added? 

@gcue

The only thing in the query that would change in order to find the missing songs for a given device, regardless of how many device names exist, is the name of the device.  So, in the query, just make the device name a variable,  As you can see in the example below, the same query is used but just a different name is passed to it each time.  Nothing changed from my previous example except making the query that reports missing songs into a function that accepts a device name.

 

Spoiler
#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "C:\Program Files\SQLite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 5');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 6');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")

        ;Display songs table
        ConsoleWrite("Temp Song Table" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT * FROM songs;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;Display list of unique song names in the songs table
        ConsoleWrite(@CRLF & "List of unique song names" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT DISTINCT name FROM songs ORDER BY name;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;List of songs on Device A
        ConsoleWrite(@CRLF & "List of all songs on Device A" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT device, name " & _
            "FROM songs " & _
            "WHERE device = 'Device A' " & _
            "ORDER BY name;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ShowMissingSongs("Device A")
        ShowMissingSongs("Device B")

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

Func ShowMissingSongs($sDeviceName)
    Local $a2DResult
    Local $iRows, $iCols

    ConsoleWrite(@CRLF & "List of all songs not on " & $sDeviceName & " and which devices have it" & @CRLF)
    _SQLite_GetTable2d(-1, _
        "SELECT name, device " & _
        "FROM songs " & _
        "WHERE name NOT IN (SELECT name from songs WHERE device = '" & $sDeviceName & "') " & _
        "ORDER BY device, name;", _
        $a2DResult, $iRows, $iCols _
    )
    _SQLite_Display2DResult($a2DResult, 10)
EndFunc

 

 

Edited by TheXman

Share this post


Link to post
Share on other sites
jchd

Here's a reworked version of @TheXman code showing how to list all files found missing on each device, all in one single query:

#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1'), " & _
                "('Device A', 'Song 5'), " & _
                "('Device B', 'Song 1'), " & _
                "('Device B', 'Song 3'), " & _
                "('Device C', 'Song 1'), " & _
                "('Device C', 'Song 3'), " & _
                "('Device C', 'Song 6'), " & _
                "('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")


        ConsoleWrite(@CRLF & "List which songs are missing on which devices" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "select name Missing, device [on device] " & _
            "from (" & _
            "       (select distinct name from songs)" & _
            "           cross join" & _
            "       (select distinct device from songs)" & _
            ") " & _
            "except " & _
            "select name, device " & _
            "from songs;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

This query first builds an unnamed temporary table of all possible couples {song, device} but removes (except verb) those found in your actual songs table. Mathematically this is a set complementation operation. This approach isn't recommended for large tables but probably good enough in such simple use cases.

Hope this gives you some incentive to look deeper into SQL and SQLite.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
gcue

a true work of art

thank you both VERY MUCH for your help.

hope both of you have wonderful holidays :)

Share this post


Link to post
Share on other sites
jchd

You can as well produce AutoIt code to copy missing file from their known-existing location:

select 'FileCopy(' ||
        '"' || substr(
                      (select device
                       from songs
                       where name = t.name
                       order by device
                       limit 1
                      ),
               -1) || ':' || name || '"' || 
        ', ' ||
        '"' || device || ':' || '"' ||
        ')' [AutoIt code]
from (
  select *
  from (
        (select distinct name from songs)
            cross join
        (select distinct device from songs)
  )               
  except
  select name, device
  from songs  
) t

This is a pretty simple query!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
gcue

would it be possible to specify just certain devices?  so i'd use my original query to generate a list of available devices then build a checkbox gui for the user to select which devices to compare.  then i would ideally plug into the query the selected devices

Local $hQuery, $aRow

    _SQLite_Open($sql_db)

    _SQLite_Query(-1, "SELECT DISTINCT Device_Label FROM aSongs", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    WEnd

    _SQLite_QueryFinalize($hQuery)

    _SQLite_Close()

thanks again! 

Share this post


Link to post
Share on other sites
TheXman
2 hours ago, gcue said:

would it be possible to specify just certain devices?

@gcue

Yes, of course it's possible.  What have you tried and what issues did you have with what you came up with?

Share this post


Link to post
Share on other sites
caramen
16 hours ago, TheXman said:

Yes, of course it's possible.  What have you tried and what issues did you have with what you came up with?

It is nice to ask what the op have tryed so far. But if he is asking if it's possible how he can even have tryed it ?

:sweating:

Edited by caramen

My video tutorials : HERE ( In construction ) 

How to Ask Help ||  UIAutomation From Junkew || WebDriver From Danp2 || And Water's UDFs in the Quote

Spoiler

 Water's UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Earthshine

It’s not worth getting steamed. Just try to help them learn. 😀


My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
TheXman
5 hours ago, caramen said:

But if he is asking if it's possible how he can even have tryed it ?

@caramen

Let me get this straight.  You pop into this topic just to ask a rhetorical question?  Who do you think you are, a wanna be Mod?  The answer is because some people that ask "is it possible..." are just looking for others to give them the code without putting in any effort,  If you would have spent any time reading through the topic, you would have seen that I provided a solid foundation for gcue to build upon already (my content is hidden under "Reveal hidden content" so it doesn't clutter up the thread due to the length of the snippet).  To add insult to injury, you didn't even provide any additional information or further the conversation.  @caramen you are a MORON!  Next time, if there's a next time, you need to stay in your lane or risk getting run over.

Obviously I don't care if I get kicked from the forum for saying that.  I've been around since 2007 and 99.9% of my posts have been in an effort to help others.  With less than 300 posts in almost 12 years, obviously my life doesn't revolve around AutoIt.  I'm a semi-retired CTO with plenty of other things to do.  I just try to give back where and when I can.  so, if I get banned, so be it.  There are plenty of other places to volunteer a little of my time.

Share this post


Link to post
Share on other sites
caramen
49 minutes ago, TheXman said:

The answer is because some people that ask "is it possible..." are just looking for others to give them the code without putting in any effort,

Give me your cristal ball please :lol:

If I am a moron, I guess you feel like: I insulted you ?

You have to stay down bro and calm yourself. Anyway I dont even would like to get you banned. I dont even report you. What is the problem about what I asked ?

It just reveal: the moron thing was the question.

52 minutes ago, TheXman said:

You pop into this topic just to ask a rhetorical question?


What is forbidden here ?

49 minutes ago, TheXman said:

If you would have spent any time reading through the topic

This is not related to what we are talking about. But yes i revealed your "Quote", so what then ?

49 minutes ago, TheXman said:

Who do you think you are, a wanna be Mod?

That is what you do actually. Think yourself too much important to judge other people. Thing that i dont did.

49 minutes ago, TheXman said:

There are plenty of other places to volunteer a little of my time.

Go for it. We want helpers but if helpers are rude like you => Goaway :) .

 

I wish you good fiesta actually you look like so happy ;).

Edited by caramen

My video tutorials : HERE ( In construction ) 

How to Ask Help ||  UIAutomation From Junkew || WebDriver From Danp2 || And Water's UDFs in the Quote

Spoiler

 Water's UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Earthshine

Caramen, How was he rude? Where you not rude to him first?

 Maybe you should learn English a little better

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
iamtheky

devolve much kids? Mindless bickering is my bag, yall can sod off. 

  • Haha 1

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
gcue

life is too short to be arguing :)

here is what i have but cant figure out where to put the string for devices

_SQLite_Open($sql_db)

    Local $iArray, $iRows, $iColumns, $iRval

    $iRval = _SQLite_GetTable2d(-1, "SELECT DISTINCT Device_Label FROM aSongs;", $iArray, $iRows, $iColumns)
    If $iRval <> $SQLITE_OK Then
        MsgBox($msg_error, @ScriptName, "Unable to generate array from SQL db.")
        Return
    EndIf

;~  Debug($iArray)

    $devices = _ArrayToString($iArray, "", 1, 0, ",")
;~  Debug($devices)

    Local $jArray, $jRows, $jColumns, $jRval

    _SQLite_GetTable2d(-1, _
            "SELECT Song_Name Missing, Device_Label [On Device]" & _
            "FROM (" & _
            "       (SELECT DISTINCT Song_Name FROM aSongs)" & _
            "           CROSS JOIN" & _
            "       (SELECT Song_Name FROM aSongs WHERE Device_Label='" & $devices & ")" & _
            ") " & _
            "EXCEPT " & _
            "SELECT Song_Name, Device_Label " & _
            "FROM aSongs;", _
            $jArray, $jRows, $jColumns _
            )
    Debug($jArray)
    
Func Debug($variable1 = "", $variable2 = "", $variable3 = "", $variable4 = "", $variable5 = "")

;~  #include <array.au3>
;~  $msg_normal = 0

    If IsArray($variable1) Or IsArray($variable2) Then
        If IsArray($variable1) Then _ArrayDisplay($variable1, $variable2)
        If IsArray($variable2) Then _ArrayDisplay($variable2, $variable1)
    Else
        $variable = ""

        If $variable1 <> "" Then $variable &= $variable1 & @CRLF
        If $variable2 <> "" Then $variable &= $variable2 & @CRLF
        If $variable3 <> "" Then $variable &= $variable3 & @CRLF
        If $variable4 <> "" Then $variable &= $variable4 & @CRLF
        If $variable5 <> "" Then $variable &= $variable5 & @CRLF

        $variable = StringStripWS($variable, 2)

        ClipPut($variable)

        MsgBox($msg_normal, "Debug", $variable)
    EndIf

EndFunc   ;==>Debug

 

Share this post


Link to post
Share on other sites
caramen
1 hour ago, iamtheky said:

devolve much kids? Mindless bickering is my bag, yall can sod off. 

Exactly what I mean. <

1 hour ago, Earthshine said:

Caramen, How was he rude? Where you not rude to him first?

He was rude at the moment i readed i am a "MORON". Does it is so impressive to you guys ? Or maybe i am alone to get that as a malicius word.

 

Well @gcue I am sorry about all that trash post. I shud it now.


My video tutorials : HERE ( In construction ) 

How to Ask Help ||  UIAutomation From Junkew || WebDriver From Danp2 || And Water's UDFs in the Quote

Spoiler

 Water's UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Earthshine

You came at him first for no reason and added nothing to the thread. Go read it. That’s considered rude btw. 


My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
TheXman

@gcue

Below is one way that it could be done.  It uses jchd's original snippet since that appears to be what you are using..

#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "c:\program files\sqlite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols
        Local $sDevices

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1'), " & _
                "('Device A', 'Song 5'), " & _
                "('Device B', 'Song 1'), " & _
                "('Device B', 'Song 3'), " & _
                "('Device C', 'Song 1'), " & _
                "('Device C', 'Song 3'), " & _
                "('Device C', 'Song 6'), " & _
                "('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")


        $sDevices = "'Device A', 'Device B'"  ;<== comma-delimited string that lists devices you want to query

        ConsoleWrite(@CRLF & "List which songs are missing on which devices" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "select name Missing, device [on device] " & _
            "from (" & _
            "       (select distinct name from songs)" & _
            "           cross join" & _
            "       (select distinct device from songs)" & _
            ") " & _
            "where device in (" & $sDevices & ") " & _
            "except " & _
            "select name, device " & _
            "from songs " & _
            "order by device, name ", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

 

Edited by TheXman
Added ORDER BY clause

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×