MySQL Find previous date that a song was played -


i'm looking why code here isn't working anymore. sql fiddle links original , modified code. had database in 2 different tables , code worked fine.

select date_format(a.show_date,'%m/%d/%y') show_date, a.song_order, a.show_id,   b.song_name, a.song_id,  (  select   ifnull(max(date_format(show_date,'%m/%d/%y')), 'new song')  tbl_shows c  a.show_date > c.show_date , a.song_id = c.song_id  ) prevdate   tbl_shows a, tbl_songs b  a.song_id = b.song_id   , a.show_id = 899 

the tables were:

create table tbl_songs (`song_id` int, `song_name` varchar(11)) ;  insert tbl_songs (`song_id`, `song_name`) values (51, 'song name a'), (368, 'song name b'), (168, 'song name c'), (568, 'song name d'), (13, 'song name e')  ;   create table tbl_shows (`song_id` int, `song_order` int, `show_date` datetime, `show_id` int)  ;   insert tbl_shows (`song_id`, `song_order`, `show_date`, `show_id`) values     (51, 1, '2013-07-19 00:00:00', 899),     (568, 2, '2013-07-19 00:00:00', 899),     (168, 3, '2013-07-19 00:00:00', 899),     (13, 4, '2013-07-19 00:00:00', 899),     (368, 1, '2013-07-06 00:00:00', 898),     (368, 1, '2013-07-03 00:00:00', 897),     (368, 1, '2013-05-27 00:00:00', 896),     (51, 1, '2013-04-10 00:00:00', 895), (168, 1, '2013-04-10 00:00:00', 895), (513, 1, '2013-03-12 00:00:00', 894), (13, 1, '2013-03-03 00:00:00', 893); 

in order database more efficient , didn't have repeat date on , on separated out table called tbl_song_shows... this.

 create table tbl_songs (`song_id` int, `song_name` varchar(11))  ;   insert tbl_songs (`song_id`, `song_name`)  values (51, 'song name a'), (368, 'song name b'), (168, 'song name c'), (568, 'song name d'), (13, 'song name e') ;  create table tbl_shows (`song_id` int, `song_order` int, `show_date` datetime, `show_id` int) ;  insert tbl_shows (`show_date`, `show_id`) values ( '2013-07-19 00:00:00', 899), ( '2013-07-19 00:00:00', 899), ( '2013-07-19 00:00:00', 899), ( '2013-07-19 00:00:00', 899), ( '2013-07-06 00:00:00', 898), ( '2013-07-03 00:00:00', 897), ( '2013-05-27 00:00:00', 896), ( '2013-04-10 00:00:00', 895), ( '2013-04-10 00:00:00', 895), ( '2013-03-12 00:00:00', 894), ( '2013-03-03 00:00:00', 893);  create table tbl_song_shows (`song_id` int, `song_order` int, `show_id` int) ;  insert tbl_song_shows (`song_id`, `song_order`, `show_id`) values  (51, 1, 899),  (568, 2, 899),  (168, 3, 899),  (13, 4, 899),  (368, 1, 898),  (368, 1, 897),  (368, 1, 896),  (51, 1, 895),  (168, 1, 895),  (513, 1, 894),  (13, 1, 893); 

i changed code reflect the additional table doesn't return same query data. please see 2 attached sql fiddle links original , modified code. i'm looking prevdate return previous date particular song played. in new code returns prevdate not based on song. me on track appreciated. thanks. bonus if afterwards tell me how count shows between show_date , prevdate. thanks!!!


Comments

Popular posts from this blog

Detect support for Shoutcast ICY MP3 without navigator.userAgent in Firefox? -

web - SVG not rendering properly in Firefox -

java - JavaFX 2 slider labelFormatter not being used -