SQL EDITOR or CUSTOM NODES Album Query by GroupDesc Field (My Ref: SQL 059)
Purpose:
The primary objective of this script is to isolate and display full Album records that contain tracks assigned to a specific category or "Grouping." This is particularly useful for users who utilize the
GroupDesc field to organize their library into sub-genres, moods, or musical movements (e.g., "Baroque," "High Fidelity," or "Workout" or “Boxed Sets”) and wish to view these collections at the album level rather than as a fragmented list of individual tracks.
Clean SQL:
Code: Select all
/* Select all columns from the Albums table
to ensure the output is grouped by Album record
*/
SELECT * FROM Albums
WHERE ID IN (
/* Subquery to identify albums containing tracks
that meet our specific criteria
*/
SELECT IDAlbum
FROM Songs
WHERE TrackType = 0 /* Ensure we are only looking at Music tracks */
AND GroupDesc IS NOT NULL /* Exclude tracks where the field is empty/null */
AND GroupDesc <> '' /* Exclude tracks with an empty string entry */
)
/* Sort the resulting album list alphabetically by the Album title */
ORDER BY Album ASC
This code logic utilizes a relational subquery structure to bridge the
Albums and
Songs tables within the Media Monkey 2024 database. By starting the query at the
Albums table, we ensure the output is compatible with album-view displays. The logic employs a
WHERE ID IN clause; this approach scans the
Songs table first to find track-level matches for the
GroupDesc criteria and then passes those unique
IDAlbum references back to the main
Albums table. This prevents duplicate album rows from appearing when multiple tracks in the same album share a grouping. Please note that while the Media Monkey API and Wiki refer to this property as
GroupDesc whereas other references use
Grouping.
SQL Editor and
Custom Nodes work using the internal database field name
GroupDesc.
Whilst
SQL Editor displays a text table,
Custom Nodes displays a more useful and graphical display of “Album Covers” sorted by Album. Clicking on a “Album Cover” then expands to display the tracks. This SQL script – whilst compatible with both add-ons - is perhaps best used in
Custom Nodes.
To run this script, makes sure you have the most recent version of the
Custom Nodes add-on and cut and paste the code into a new Album node using the right-click configure option! Similarly, the code can be cut and pasted into the
SQL Editor query box then press the Execute button. SQL Editor is useful as it "colour-codes" the script e.g. Blue for SQL commands, Red for field names, White for comments...
https://www.mediamonkey.com/addons/brow ... tom-nodes/
https://www.mediamonkey.com/addons/brow ... ql-editor/
Acknowledgements
Drakinite, Ludek, Rusty for their work on these add-ons.
[b][i][u]SQL EDITOR or CUSTOM NODES Album Query by GroupDesc Field (My Ref: SQL 059)[/u][/i][/b]
[b]Purpose:[/b]
The primary objective of this script is to isolate and display full Album records that contain tracks assigned to a specific category or "Grouping." This is particularly useful for users who utilize the [b][i]GroupDesc[/i][/b] field to organize their library into sub-genres, moods, or musical movements (e.g., "Baroque," "High Fidelity," or "Workout" or “Boxed Sets”) and wish to view these collections at the album level rather than as a fragmented list of individual tracks.
[b]Clean SQL:[/b]
[code]/* Select all columns from the Albums table
to ensure the output is grouped by Album record
*/
SELECT * FROM Albums
WHERE ID IN (
/* Subquery to identify albums containing tracks
that meet our specific criteria
*/
SELECT IDAlbum
FROM Songs
WHERE TrackType = 0 /* Ensure we are only looking at Music tracks */
AND GroupDesc IS NOT NULL /* Exclude tracks where the field is empty/null */
AND GroupDesc <> '' /* Exclude tracks with an empty string entry */
)
/* Sort the resulting album list alphabetically by the Album title */
ORDER BY Album ASC
[/code]
[b]Provenance & Compatibility:
[/b]This code logic utilizes a relational subquery structure to bridge the [b]Albums [/b]and [b]Songs [/b]tables within the Media Monkey 2024 database. By starting the query at the [b]Albums[/b] table, we ensure the output is compatible with album-view displays. The logic employs a [i]WHERE ID IN[/i] clause; this approach scans the [b]Songs[/b] table first to find track-level matches for the [b]GroupDesc[/b] criteria and then passes those unique [b]IDAlbum[/b] references back to the main [b]Albums[/b] table. This prevents duplicate album rows from appearing when multiple tracks in the same album share a grouping. Please note that while the Media Monkey API and Wiki refer to this property as [b]GroupDesc[/b] whereas other references use [b]Grouping[/b]. [b][i]SQL Editor[/i][/b] and [b][i]Custom Nodes[/i][/b] work using the internal database field name [b]GroupDesc[/b].
Whilst [b][i]SQL Editor[/i][/b] displays a text table, [b][i]Custom Nodes[/i][/b] displays a more useful and graphical display of “Album Covers” sorted by Album. Clicking on a “Album Cover” then expands to display the tracks. This SQL script – whilst compatible with both add-ons - is perhaps best used in [b][i]Custom Node[/i][/b]s.
To run this script, makes sure you have the most recent version of the [b][i]Custom Nodes[/i][/b] add-on and cut and paste the code into a new Album node using the right-click configure option! Similarly, the code can be cut and pasted into the [b][i]SQL Editor[/i][/b] query box then press the Execute button. SQL Editor is useful as it "colour-codes" the script e.g. Blue for SQL commands, Red for field names, White for comments...
https://www.mediamonkey.com/addons/browse/item/custom-nodes/
https://www.mediamonkey.com/addons/browse/item/sql-editor/
[b][i][u]Acknowledgements[/u][/i][/b]
Drakinite, Ludek, Rusty for their work on these add-ons.