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')