stuck on a query to identify unique items

[gcue]

Awesome thank you very much!! 

[gcue]

hmm there is a slight problem or perhaps misunderstanding :).  i changed the song names to show what i mean from the example script.  if i am comparing devices A and B, songs C should not show up as missing songs. only songs that are missing from both A and B 

expandcollapse popup

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

example()

Func example()
        Const $SQLITE_DLL = "c:\windows\system32\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 A1'), " & _
                "('Device A', 'Song A5'), " & _
                "('Device B', 'Song B1'), " & _
                "('Device B', 'Song B3'), " & _
                "('Device C', 'Song C1'), " & _
                "('Device C', 'Song C3'), " & _
                "('Device C', 'Song C6'), " & _
                "('Device C', 'Song C8');" _
        )
        _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

 

[TheXman]

Yes, I think I may have misunderstood your modified request to be able to select devices to compare.  I thought that you wanted the original list but just to show the information for the selected devices.  In other words, for the selected devices, you wanted to see all of the songs, across all of the devices, that were missing from those 2 devices.  That's what your original request was looking for, all songs missing from each device and which device had them.  At least that's how I understood it.

So is your plan to be able to select just 2 devices to compare or are you wanting to be able to select 2 or more devices in which to compare?   If you just want to compare 2 devices, is one device the target device and the other device is the source device?  Meaning, do you just want to know which songs on the source device are missing on the target device?  Or, should the result set show which songs are missing on each of the selected devices?  If it is more than 2 devices, is one device the target and the other devices are source devices?  You see where I'm going with this.  As you can see, your request to be able to compare devices was a little light on details.  The more detailed you can make your request, the more accurate the solution will be and it will eliminate a bunch of back and forth, which can be frustrating.    It would also be helpful if you were to show a desired result set based on a given selection.  For instance, if Devices A & B are selected, the result should be something like...

Edited December 30, 2018 by TheXman

[gcue]

sorry for the misunderstanding.  yes - the result set should show which songs are missing on each of the selected devices (2 or more). i guess technically each of the devices is a source and target because i am trying to show which songs are missing so each device would have exactly the same songs in the end.  but for reasons mentioned earlier, i do not always want each of the devices to have all the songs so i would pick and choose from the results.

here is an example of what i am trying to do:

there are 4 devices (but can be more)

Quote

 

Device A has

song 1

song 3

song 4

song 5

Device B has

song 1

song 2 

song 4

Device C has

song 2

song 3

song 4

song 5

Device D has

song 1

song 2

song 3

 

song 1 is missing from device C

song 2 is missing from device A

song 3 is missing from device B

song 4 is missing from device D

song 5 is missing from device B, D

so its getting a list of all the songs from the devices selected and then showing what's missing from each of the devices from that list

thank you again!!

 

[TheXman]

The example below appears to produce your desired result set.  I modified the table to include the entries that you gave as an example in the post above.  As you can see, the only change was to add WHERE clauses to the cross joined select statements that included the selected devices.

expandcollapse popup

#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, song);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1'), ('Device A', 'Song 3'), ('Device A', 'Song 4'), ('Device A', 'Song 5'), " & _
                "('Device B', 'Song 1'), ('Device B', 'Song 2'), ('Device B', 'Song 4'), " & _
                "('Device C', 'Song 2'), ('Device C', 'Song 3'), ('Device C', 'Song 4'), ('Device C', 'Song 5'), " & _
                "('Device D', 'Song 1'), ('Device D', 'Song 2'), ('Device D', 'Song 3');" _
        )
        _SQLite_Exec(-1, "COMMIT;")

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

        ConsoleWrite(@CRLF & "List of missing songs" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT song Missing, device [On Device] " & _
            "FROM (" & _
            "     (SELECT DISTINCT song FROM songs WHERE device IN (" & $sDevices & "))" & _
            "      CROSS JOIN" & _
            "     (SELECT DISTINCT device FROM songs WHERE device IN (" & $sDevices & "))" & _
            "     ) " & _
            "EXCEPT " & _
            "SELECT song, device " & _
            "FROM songs " & _
            "WHERE device IN (" & $sDevices & ") " & _
            "ORDER BY song, device; ", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

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

EndFunc

 

Result

List of missing songs
 Missing     On Device  
 Song 1      Device C   
 Song 2      Device A   
 Song 3      Device B   
 Song 4      Device D   
 Song 5      Device B   
 Song 5      Device D

 

Edited December 30, 2018 by TheXman
Tidy up SQL

[gcue]

its beautiful *sniff*sniff* 

thank you VERY much! 

[gcue]

i added another field in addition to song and device.  i added the field id3.  i was able to get it working so it still shows differences when comparing against song but i would like to change the comparing field to id3 and am having difficulty.  so in case that didnt make sense, i would like to show the differences between devices when comparing against the id3 field but still include the other two fields (song and device) in the resulting array. 

here is what i have so far

_SQLite_GetTable2d(-1, _
            "SELECT song Missing, id3, device [On Device]" & _
            "FROM (" & _
            "       (SELECT DISTINCT song, id3 FROM Songs WHERE device IN (" & $sDevices & "))" & _
            "           CROSS JOIN" & _
            "       (SELECT DISTINCT device FROM Songs WHERE device IN (" & $sDevices & "))" & _
            ") " & _
            "EXCEPT " & _
            "SELECT song, id3, device " & _
            "FROM Songs " & _
            "WHERE device IN (" & $sDevices & ") " & _
            "ORDER BY song; ", _
            $jArray, $jRows, $jColumns _
            )

thanks in advance

Edited January 12, 2019 by gcue

[TheXman]

Can you provide sample data and the expected result? 

Does every entry in the database have an id3 value or can some be blank/null? 

If the same song, on different devices, have different id3 tags, I would assume that the different id3 values would appear as missing on each of those devices, correct?

[gcue]

so id3 is a file attribute which can help serve as an identifier for a song.  you can store artist, song title, album etc.  anyway i have a method to get the id3 tag for the songs and i am storing them in the sql database for each of the song record items.  you are right its possible some songs might not have an id3 configured so the column entry would be blank for that record item.

the tags should be exactly the same to match. if they do not then they are not a match.  so the logic should be exactly the same instead of matching the song we would be matching the id3 field.. the only differnce now is 3 columns (song, device, id3) in the results instead of just 2 (song, device).

example data set:

device A, song - title 1, song - id3 1

please let me know if i need to be more specific

thanks again!

 

 

 

[TheXman]

I didn't test this very well but I think it should suffice.  You can play with the data and see if the query holds up.

expandcollapse popup

#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, song, id3);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1', 'id3-1'), ('Device A', 'Song 3', ''), ('Device A', 'Song 4', 'id3-4'), ('Device A', 'Song 5', 'id3-5'), " & _
                "('Device B', 'Song 1', 'id3-1'), ('Device B', 'Song 2', 'id3-2'), ('Device B', 'Song 4', 'id3-4'), " & _
                "('Device C', 'Song 2', 'id3-2'), ('Device C', 'Song 3', ''), ('Device C', 'Song 4', 'id3-4'), ('Device C', 'Song 5', 'id3-5'), " & _
                "('Device D', 'Song 1', 'id3-1'), ('Device D', 'Song 2', 'id3-2'), ('Device D', 'Song 3', ''); " _
        )
        _SQLite_Exec(-1, "COMMIT;")

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

        ConsoleWrite(@CRLF & "List of missing songs" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT device [Device], song_tag [Missing Song (ID3)] " & _
            "FROM (" & _
            "     (SELECT DISTINCT device FROM songs WHERE device IN (" & $sDevices & "))" & _
            "      CROSS JOIN" & _
            "     (SELECT DISTINCT (song || ' (' || id3 || ')') [song_tag] FROM songs WHERE device IN (" & $sDevices & "))" & _
            "     ) " & _
            "EXCEPT " & _
            "SELECT device, song || ' (' || id3 || ')' " & _
            "FROM songs " & _
            "WHERE device IN (" & $sDevices & ") " & _
            "ORDER BY device, song_tag; ", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 20)

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

EndFunc

Output:

List of missing songs
 Device                Missing Song (ID3)   
 Device A              Song 2 (id3-2)       
 Device B              Song 3 ()            
 Device B              Song 5 (id3-5)       
 Device C              Song 1 (id3-1)       
 Device D              Song 4 (id3-4)       
 Device D              Song 5 (id3-5)

 

Edited January 12, 2019 by TheXman
Added output

[gcue]

still have to go through the results.  any chance the id3 tag can be in its own column in the array instead of in parenthesis for the song column?

thank you so much for your help!

[TheXman]

17 minutes ago, gcue said:

any chance the id3 tag can be in its own column in the array instead of in parenthesis for the song column

The short answer is yes.  However, I tried to keep everything as close as possible to the way that you were doing things currently.  I was focused on getting an accurate result set which could be processed as needed.  As your requirements grow, the ability to keep this to a single sqlite query becomes exponentially more difficult.

Edited January 12, 2019 by TheXman

[gcue]

sorry thought the changes were going to be subtle (just adding another column and using that column as the comparing field) didnt expect it to be so complicated... sorry

[TheXman]

13 minutes ago, gcue said:

thought the changes were going to be subtle

No worries. 

"It should easy to just <fill in the blank>" or "I thought it would have been easy to just<fill in the blank>".   Any body who's been coding for others, for any length of time, has heard those type of statements more times than they care to count. 

[TheXman]

.

Edited January 13, 2019 by TheXman
Removed double post

[TheXman]

17 hours ago, gcue said:

any chance the id3 tag can be in its own column in the array instead of in parenthesis for the song column?

@gcue

Try this.  It is basically the same as what you suggested except the columns are reordered. 

What did you think was wrong with your query?  It appeared to work just fine.  I should have tried it before doing my own thing.    In any case, as you can see, there are usually many ways to get to the same result.

 

expandcollapse popup

#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, song, id3);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1', 'id3-1'), ('Device A', 'Song 3', ''), ('Device A', 'Song 4', 'id3-4'), ('Device A', 'Song 5', 'id3-5'), " & _
                "('Device B', 'Song 1', 'id3-1'), ('Device B', 'Song 2', 'id3-2'), ('Device B', 'Song 4', 'id3-4'), " & _
                "('Device C', 'Song 2', 'id3-2'), ('Device C', 'Song 3', ''), ('Device C', 'Song 4', 'id3-4'), ('Device C', 'Song 5', 'id3-5'), " & _
                "('Device D', 'Song 1', 'id3-1'), ('Device D', 'Song 2', 'id3-2'), ('Device D', 'Song 3', ''); " _
        )
        _SQLite_Exec(-1, "COMMIT;")

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

        ConsoleWrite(@CRLF & "Selected devices: " & $sDevices & @CRLF & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT " & _
            "   device, " & _
            "   song [missing song], " & _
            "   id3 " & _
            "FROM ( " & _
            "     (SELECT DISTINCT device FROM songs WHERE device IN (" & $sDevices & ")) " & _
            "     CROSS JOIN " & _
            "     (SELECT DISTINCT song, id3 FROM songs WHERE device IN (" & $sDevices & ")) " & _
            "     ) " & _
            "EXCEPT " & _
            "SELECT device, song, id3 " & _
            "FROM songs " & _
            "WHERE device IN (" & $sDevices & ") " & _
            "ORDER BY device, song; ", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 15)

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

EndFunc

Output:

Selected devices: 'Device A', 'Device B', 'Device D'

 device           missing song     id3             
 Device A         Song 2           id3-2           
 Device B         Song 3                           
 Device B         Song 5           id3-5           
 Device D         Song 4           id3-4           
 Device D         Song 5           id3-5

 

Edited January 13, 2019 by TheXman
Updated snippet and output

[gcue]

the output format looks great.  however the data looks a bit off.  i think the comparison is still using the song field instead of id3 field.  for instance, if i change the song name in Device B (from song 1 to song 9) and the id3 field remains the same - it should not come up on the missing results for Device A or Device D because the same id3 tag (id3-1) exists on both Device A and D. the song name doesnt matter because the only comparison field is the id3 field.  again i apologize for the misunderstanding.  hopefully its not to difficult to make the adjustment!

nevertheless thank you very much for your help!

[TheXman]

The query uses a combination of the song and the ID3 tag, together.  The reason you cannot use just the ID3 tag alone is because you said that some songs may not have an ID3 tag.  So for songs without an ID3 tag, how would you identify them or be able to tell if they are missing from another device? 

[gcue]

I think if they do not have an id3 tag they would automatically be included in the missing list with an empty id3 field 

[TheXman]

Let me try to explain this another way.  Assume that you have the following data:

dev 1 / song 1 /  id-1
dev 1 / song 2 / <blank>
dev 2 / song 1 / id-1

 

According to your logic, the comparison data would be:

dev 1 / id-1
dev 1 / <blank>
dev 2 / id-1
 

If you only compare using device id and id3, how would you identify that song2 is missing from dev 2?  You couldn't.  You would need to take into account the song title.  Therefore, the query uses a combination of song title AND id3 to identify the missing songs.

Edited January 14, 2019 by TheXman