How to create a 'list within a list' using a sql temp table

Question

How to create a 'list within a list' using a sql temp table and then search threw it and do matching.

 

-----------

 

In a stored procedure.

I will read a DB table and build an internal table in SQL server to build 'My list'.

 

CREATE TABLE #t1_MyList

(

#t1_SelectionId int

)

'My list' data:

1

2

3

4

5

6

 

I will then read a DB table and build an internal table in SQL server to build a list of users and their selections. Each can have a different number of entries.

 

CREATE TABLE #t2_UsersSelectionsList

(

#t2_UserId int,

#t2_SelectionId int

)

 

-- Holds a User's selections data.

-- 1, 1

-- 1, 2

-- 1, 3

-- 1, 7

-- 1, 8

-- 1, 9

-- 2, 1

-- 2, 9

-- 3, 1

-- 3, 2

-- 3, 3

-- 3, 4

-- 3, 5

-- 3, 6

-- 4, 1

-- 4, 2

-- 4, 3

-- 4, 4

-- 4, 8

-- 4, 9

-- 5, 7

-- 5, 8

-- 5, 11

-- 6, 1

-- 6, 2

-- 6, 7

-- 6, 8

-- 6, 9

-- 6, 10

-- 6, 13

-- 6, 14

-- 7, 50

 

I then think I need to group each user and load into a 'list of lists.

 

CREATE TABLE #t3_AllUsersSelectionsListOfLists

(

#t3_UserId int,

#t3_ListOfLists ? -- How to define this? A list of by user

-- in the t2_UsersSelectionsList.

)

 

-- ‘All users selections list of lists’ data:

-- user 1:

-- 1, 2, 3, 7, 8, 9

-- user 2:

-- 1, 9

-- user 3:

-- 1, 2, 3, 4, 5, 6

-- user 4:

-- 1, 2, 3, 4, 8, 9

-- user 5:

-- 7, 8, 11

-- user 6:

-- 1, 2, 7, 8, 9, 10, 13, 14

-- user 7:

-- 50

 

I then will create 5 buckets to contain users.

 

CREATE TABLE #t4_ZeroPercentMatchList

(

#t4_UserId int

)

CREATE TABLE #t5_LessThan50PercentMatchList

(

#t5_UserId int

)

CREATE TABLE #t6_50PercentMatchList

(

#t6_UserId int

)

CREATE TABLE #t7_75PercentMatchList

(

#t7_UserId int

)

CREATE TABLE #t8_100PercentMatchList

(

#t8_UserId int

)

 

I want to match 'My list' to the #t3_AllUsersSelectionsListOfLists.

Iterating threw each entry in 'My list' to find matches in #t3_AllUsersSelectionsListOfLists. Iterating threw each entry in #t3_ListOfLists. Putting the matched entries 'user' into a bucket per percent of match.

 

CREATE TABLE #t9_BucketsListOfLists

(

#t9_MatchBucket varchar(5), -- '0%', '<50%', '50%', '75%', '100%'

#9_ListOfLists ? -- How to define this? A list of all:

-- #t4_ to #t8_.

)

 

-- Result - a 5 entry 'list of lists':

-- 0% bucket - 'My list' had no matches to this user(s)

-- user 5

-- user 7

-- <50% bucket - 'My list' had <50% to this user(s)

-- user 6

-- 50% bucket - 'My list' had a 50% to these user(s)

-- user 1

-- user 2

-- 75% bucket - 'My list' had a 75% to this user(s)

-- user 4

-- 100% bucket - 'My list' had a 100% to this user(s)

-- user 3

 

I will then return the #t9_BucketsListOfLists (the 5 percent buckets) a ‘list of lists’ back to the calling ASP.net web page

 

How do I write this in SQL server?

How do I set up this temp table structure for 'list of lists'?

Do I use a cursor to search or is there a better way?