SQL Query Help for those Interested

The following is a script that was originally posted by Marius
-----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
ORDER BY `date_played` ASC
LIMIT 2;
------

In the SQL world this is basically a nested select statement with some conditionals added. I will simplify this and remove 4 lines that I will describe later.


----- query with 4 lines removed -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=(
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1)
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
------------------------------------------

Unless I have fat fingered something, this last SQL statement should work using HeidiSQL. Now I'll split this up into the two select(s). I will start with the inner one
because that select is what provides the data to the outer select statement.

----- inner select -----
SELECT `artist` FROM `songs`
WHERE `enabled`=1
AND `song_type`=0
GROUP BY `artist`
HAVING COUNT(*) > 1
ORDER BY `date_played` ASC LIMIT 1
-------------------------

And here is the outer one ( Note that this select statement returns *TWO* items one being the "ID" which is what RadioDJ uses to fetch the song.

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------

If the inner select is run using HeidiSQL you should be able to see that it returns a single item. Which is an Artist.
Select `artist` FROM `songs` [ See ? this says "Show me all of the Artist fields from the songs table ]

This returns something like:

Pink Floyd
Buddy Holly
Meatloaf
Pink Floyd
etc etc etc

This result will be presented to you in the order that is finds them in the songs table. And it will display all occurrences of each artist but not in any particular order.
Now lets apply the conditional(s)


WHERE `enabled`=1 [ Only show the enabled songs ]

AND `song_type`=0 [ Only select songs]

Track types:

Music = 0
Jingle = 1
Sweeper = 2
Voiceover = 3
Commercial = 4
InternetStream = 5
Other = 6
VDF = 7
Podcast = 8
Request = 9
News = 10
PlaylistEvent = 11
FileByDate = 12
NewestFromFolder = 13

Teasers = 14

---------------------------


GROUP BY `artist` [ All results of a particular artist will be grouped together ]

HAVING COUNT(*) > 1 [ count(*) is a shorthand for *number of rows* so only show those results numbering more than 1 ]
This makes sense right ? How can you play two songs from an artist that only has a single song in the Database ?


ORDER BY `date_played` ASC LIMIT 1 [ This will ORDER the results displayed by *date_played* in ASCENDING (ASC) Order and only show 1 (ONE) result]

For fun try that query and change LIMIT 1 to LIMIT 10 and you'll get the idea You can also change the ASC (Ascending) to DESC (Descending)


So to recap, this simple query provides a result consisting of Artists having at least two songs in the songs table.

The outer Query then takes this artist info and operates on that. Here is the outer query again.

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------


Let's say that the Artist Name returned is "Pink Floyd"

----- outer select -----
SELECT `ID`, `artist` FROM `songs` WHERE `artist`=`Pink Floyd`
AND `enabled`=1
AND `song_type`=0
ORDER BY `date_played` ASC
LIMIT 2;
-------------------------

Can you see that the inner query simply provides that info to the outer query ?

That's all the simple stuff. But for completeness here are the 4 lines that I removed.

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
----

And this one too

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$))
----


These conditionals rely on variables not readily available to HeidiSQL. But it's easy to get around this because these variables are defined within RadioDJ in the repeat options.
So you can simply replace the variables with the actual data from your options.
Let us assume that the $TrackRepeatInterval$ is defined in your options to be 600 minutes and $ArtistRepeatInterval$ is set to 300 minutes

To use HeidiSQL to examine this query we need to replace those variables with the values. This is because HeidiSQL doesn't know about these variables. So just use their value

----
AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))
AND ((TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > 600) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > 300))
----

Please be aware that this doesn't really provide a RANDOM data though for most users this might well be enough.

Using HeidiSQL you can look at all the various RadioDJ tables and the fields within each one and possibly write php scripts to form other queries that you might find useful.

Hope this helps

By Forum member wrm  (Bill)

-- 10 tracks o0n genre name

SELECT s.ID
FROM songs s
INNER JOIN genre g ON s.id_genre = g.id
WHERE g.name = 'Acapella'
ORDER BY RAND()
LIMIT 10;


PS: any errors contained in this are all mine