CODEWITHSUNDEEP
mysqlsql
Dax
Dax

Reputation: 11

How to find proper User between two tables

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.

Upvotes: 1

Views: 47

Answers (1)

Rogério Carvalho Batista
Rogério Carvalho Batista

Reputation: 462

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

Upvotes: 1

Related Questions