SQLCe 4.0 Select where fieldOrder

Transact-SQL
1

I am back trying to get this to work (after a couple years). It's for SQLCe 4.0.
Below is some test script that anyone can copy and run to test.
The @Keyword table would be what the user inputs.
These would be the song ID's.
Each show has song ID's and a SongOrder field.
SongOrder Must Be Consecutive.
So, the user is looking for a show(s) with that particular song order.
Each TABLE param created in the test script mirrors the actual db.
The SELECT should only return 1546 but it's not playing nice.

--Test two shows, years apart
DECLARE @Shows TABLE (ID INT)
INSERT INTO @Shows (ID) VALUES (1)
INSERT INTO @Shows (ID) VALUES (1546)

--Add a few songs
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')
INSERT INTO @Songs (ID, Name) VALUES (322, 'Song 6')

--Where the shows and songs get their reference
DECLARE @ShowSongRef TABLE (ID INT, ShowID INT, SongID INT, SongOrder INT, PRIMARY KEY (ID))

--Show 1
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)
--Show 1546
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)
INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (9, 1546, 322, 5)

--The song order we are looking for
--Doesn't matter where in the show the order occurs
DECLARE @Keyword TABLE (ID INT)
INSERT INTO @Keyword (ID) VALUES ('654')
INSERT INTO @Keyword (ID) VALUES ('321')
INSERT INTO @Keyword (ID) VALUES ('322')

--This should only return 1546
SELECT DISTINCT sh.ID AS ShowID--, s.Name 
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 --remove the +1 and it works but are they in order?
		AND ref2.ShowID = ref.ShowID

INNER JOIN @Shows sh ON sh.ID = ref.ShowID
WHERE ref.SongID IN (SELECT ID FROM @Keyword)

GROUP BY sh.ID
HAVING COUNT(sh.ID) = (SELECT COUNT(ID) FROM @Keyword)
--ORDER BY ref2.SongOrder
2

Create Table #Shows (ID INT)
INSERT INTO #Shows (ID) VALUES (1)
INSERT INTO #Shows (ID) VALUES (1546)

--Add a few songs
Create table #Songs (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')
INSERT INTO #Songs (ID, Name) VALUES (322, 'Song 6')

--Where the shows and songs get their reference
Create table #ShowsongRef (ID INT, ShowID INT, SongID INT, SongOrder INT, PRIMARY KEY (ID))

--Show 1
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)
--Show 1546
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)
INSERT INTO #ShowsongRef (ID, ShowID, SongID, SongOrder) VALUES (9, 1546, 322, 5)

--The song order we are looking for
--Doesn't matter where in the show the order occurs
Create Table #Keyword (ID INT)
INSERT INTO #Keyword (ID) VALUES ('654')
INSERT INTO #Keyword (ID) VALUES ('321')
INSERT INTO #Keyword (ID) VALUES ('322')

--This should only return 1546 and 1 because the SongID 321 is in ShowID 1 and 1546

SELECT s.Name, *
FROM #Songs s
INNER JOIN #ShowsongRef ref ON ref.SongID = s.ID
INNER JOIN #Shows sh
ON sh.ID = ref.ShowID
join #Keyword k
on k.ID = ref.SongID

3

But I don't want ShowID 1 because there are two songs not in that show. That's why there is a HAVING clause. The problem is finding a show with those songs in that exact order.

4

This seems to work

SELECT sh.ID AS ShowID, count(1)
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 --remove the +1 and it works but are they in order?
-- AND ref2.ShowID = ref.ShowID

INNER JOIN #Shows sh ON sh.ID = ref.ShowID
WHERE ref.SongID IN (SELECT ID FROM #Keyword)

GROUP BY sh.ID
HAVING COUNT(sh.ID) = (SELECT COUNT(ID) FROM #Keyword)

5

Removing the second join is the same as removing the +1 as I mentioned in the comments.
But if you adjust the Keyword table, mix up the input order of the user, it's the same results.
The songs must be in order according to the SongOrder field.
It doesn't matter if that field is 1,2,3 or 5,6,7. But in this case it's 3,4,5.

6

How do you determine order? are there Identity columns or dates to determine this in #Songs and #keyWords?

7

The order of the songs to search for is in keywords. The ShowSongRef table has the order of songs per show in the SongOrder field. So, I'm looking for songs 1,2,3. I find those songs in the ref table in one show, but are they in order? That is does the ref table list those songs as 3,4,5 or 6,7,8, or 2,3,4... etc. Consecutive. I could easily add a primary key to the KeyWords table if that is the issue (because you got me thinking about it)