SQLTeam.com | Weblogs | Forums

SQL Ce 4.0 Select records in order by sort column

tsql

#1

I've been trying to get this working for a while now, so time to ask. I'm using SQLCe 4.0 and the sql below brings back the records but I'm trying to add one more requirement to it. In the ShowSongRef table, there is the SongOrder column. I only want to bring back the show where the songs are in order.

In other words, for the SongID's hardcoded below, they need to have a SongOrder of 1 and 2 or 4 and 5, any combination as long as it's in order, one song played right after the other and it could be a group of songs (more than two).

Here's some sql to actually run and test

The Select below is not working.

DECLARE @Shows TABLE (ID INT)
INSERT INTO @Shows (ID) VALUES (1)
INSERT INTO @Shows (ID) VALUES (1546)

DECLARE @Songs TABLE (ID INT, Name VARCHAR(256))
INSERT INTO @Songs (ID, Name) VALUES (1, 'Song 1')
INSERT INTO @Songs (ID, Name) VALUES (2, 'Song 2')
INSERT INTO @Songs (ID, Name) VALUES (3, 'Song 3')
INSERT INTO @Songs (ID, Name) VALUES (654, 'Song 4')
INSERT INTO @Songs (ID, Name) VALUES (321, 'Song 5')

DECLARE @ShowSongRef TABLE (ID INT, ShowID INT, SongID INT, SongOrder INT)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (1, 1, 1, 1)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (2, 1, 2, 2)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (3, 1, 321, 3)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (4, 1, 3, 4)

INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (5, 1546, 3, 1)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (6, 1546, 1, 2)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (7, 1546, 654, 3)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (8, 1546, 321, 4)    

--NO ROWS RETURNED!
SELECT sh.ID FROM @Songs s 
INNER JOIN @ShowSongRef ref  ON ref.SongID = s.ID 
INNER JOIN @ShowSongRef ref2  ON ref2.SongID = s.ID 
 and Ref2.SongOrder = Ref.SongOrder + 1 
 and ref.ShowID = ref2.ShowID 
INNER JOIN @Shows sh ON sh.ID = ref.ShowID 
WHERE ref.SongID IN ('654', '321')