How to find proper User between two tables

Question

I have something like this in two tables on my database

"User" table

userId         viewsIDS       
  1            1,2,3,4,5        
  2           2,4,6,12,13       
  3             1,4,5           
 ...             ...

"View" table

viewID        webpageId       
  1              1            
  2              1            
  3              2            
  4              3
  5              3
  6              3            
 ...            ...

Now, which query should I use to find user IDs just having the webpage IDs?

I guess should somehow group "viewIDs" from "View" table and then check if one of these IDs is in the User "viewIDS" field so take the userID out, but I don't know which query would make this the better way possible.

you have the wrong data structure you should normalize your column viewsId in a proper related table avoinding string separeated collection of values — ScaisEdge, Nov 24 '17 at 16:41
man your database structure sucks. lucky you i found [this](https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column). hope it helps — Franck Ngako, Nov 24 '17 at 16:43
I know data structure sucks, unfortunately it's not one of my projects, but a customer just asked for help to select this kind of data, I don't even want to try and mess with that db more than necessary, thanks for help however, will try that — Dax, Nov 24 '17 at 17:12

score 1 · Answer 1 · edited Nov 25 '17 at 14:26

You can try this:

CREATE TABLE #USER (userId INT, viewsId VARCHAR(50))
CREATE TABLE #VIEW (viewId INT, webPageId INT)

INSERT INTO #USER
        SELECT 1, '1,2,3,4,5'
UNION   SELECT 2, '2,4,6,12,13'
UNION   SELECT 3, '1,4,5'

INSERT INTO #VIEW 
        SELECT  1,1            
UNION   SELECT  2,1            
UNION   SELECT  3,2            
UNION   SELECT  4,3
UNION   SELECT  5,3
UNION   SELECT  6,3


SELECT u.userId, v.webPageId FROM #USER u
CROSS APPLY fnSplitString(viewsId, ',') s
INNER JOIN #VIEW v ON s.splitdata = v.viewId
ORDER BY u.userId

I copy this function fnSplitString in:

http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

But as was told before, you should normalize your table ;)

How to find proper User between two tables

1 Answers1