Chunkywizard wrote:
> Hi Erland,
>
> I am trying to make an SQL playlist that plays singles from a Random
> Year. I started with the SQL Playlist 'Random Years' but I want to add
> in 'comment=single' to it. Looking at other playlists that do this it
> seems the code is:
>
> >
Code:
--------------------
> > join comments on
> tracks.id=comments.track and comments.value like 'single'
--------------------
> >
>
> but where do I add it in the code? The full SQL is:
>
Try something like this:
Code:
--------------------
-- PlaylistName:Single From A Random Year
-- PlaylistGroups:
-- PlaylistOption Unlimited:1
create temporary table sqlplaylist_random_years as
select tracks.year as year from tracks
join comments on
tracks.id=comments.track and comments.value like
'single'
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and
dynamicplaylist_history.client='PlaylistPlayer'
left join track_statistics on
tracks.url=track_statistics.url
where
audio=1 and
tracks.year is not null and
tracks.year!=0
and dynamicplaylist_history.id is null
and not exists (select * from tracks
t2,genre_track,genres
where
t2.id=tracks.id
and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in
('Children''s Music','Comedy','Holiday','Soundtrack'))
group by tracks.year
having
max(ifnull(track_statistics.lastplayed,0))<(unix_timestamp()-604800)
order by random()
limit 1;
select tracks.url from tracks
join sqlplaylist_random_years
on tracks.year=sqlplaylist_random_years.year
join comments on
tracks.id=comments.track and comments.value like 'single'
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and
dynamicplaylist_history.client='PlaylistPlayer'
where
audio=1
and dynamicplaylist_history.id is null
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in
('Children''s Music','Comedy','Holiday','Soundtrack'))
group by tracks.id
order by sqlplaylist_random_years.year,random()
limit 20;
drop table sqlplaylist_random_years;
--------------------
As you can see, it's added in both select statements, the first will
make sure only years which contains singles are included and the second
one will make sure only the singles of the selected year will be
included.
I haven't tried it myself but I think it should work as you like.
------------------------------------------------------------------------
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=49483
_______________________________________________
plugins mailing list
plugins@lists.slimdevices.com
http://lists.slimdevices.com/mailman/listinfo/plugins
