locked
Match entry with multiple keywords RRS feed

  • Question

  • I have one table with a list of people with a unique ID, I then have a second table which contains a list of keywords associated with each person, with one entry per keyword.  I can obviously generate a list of people with brown hair but what about all those with brown hair and blue eyes?  What is the best way to approach this in SQL?  I have structured the table in this way as each person may have 100 different keywords and possible 500+ users.

    Table 1

    userID | Name

    ----------------------------------

    1         | John Doe

    2         | Jane Doe

    3         | Joe Blogs

    Table 2

    KeywordID | UserID | Keyword          

    ----------------------------------------------------------

    1               |   1        | Brown Hair     

    2               |   1        | Blue Eyes     

    3               |   2        | Brown Hair     

    4               |   2        | Brown Eyes     

    5               |   3        | Blonde Hair     

    6               |   3        | Brown Eyes

    7               |   4        | Blonde Hair     

    8               |   4        | Blue Eyes

    Wednesday, February 27, 2019 11:03 AM

Answers

  • Please use the query below

    DECLARE @Table1 table (UserId INT, Name VARCHAR(25))
    
    INSERT INTO @Table1
    SELECT 1, 'John Doe' union all
    SELECT 2, 'Jane Doe' union all 
    SELECT 3, 'Joe Blogs'
    
    DECLARE @Table2 TABLE (KeywordID INT, UserID INT, Keyword NVARCHAR(35))
    
    INSERT INTO @Table2
    SELECT 1,  1, 'Brown Hair' union all     
    SELECT 2,  1, 'Blue Eyes'  union all   
    SELECT 3,  2, 'Brown Hair' union all
    SELECT 4,  2, 'Brown Eyes' union all
    SELECT 5,  3, 'Blonde Hair' union all  
    SELECT 6,  3, 'Brown Eyes' union all
    SELECT 7,  4, 'Blonde Hair' union all
    SELECT 8,  4, 'Blue Eyes'
    
    
    SELECT NAME
    FROM @Table1
    WHERE UserId IN (
    		SELECT a.userid
    		FROM @table1 a
    		INNER JOIN @Table2 b ON a.UserId = b.UserID
    		WHERE b.Keyword IN (
    				'Brown Hair'
    				,'Blue Eyes'
    				)
    		GROUP BY a.UserId
    		HAVING count(DISTINCT keywordId) = 2
    		)
    
    

    Results:

    John Doe

    Regards, Dineshkumar,
    Please Mark as Answer if my post answers your question and Vote as Helpful if it helps you

    DWBI Blog


    Wednesday, February 27, 2019 11:25 AM
  • Do you mean how can you select this structure?

    Like:

    Create table #forum1 (userid int, name varchar(20))
    insert into #forum1 values (1,'John Doe'),(2,'Jane Doe'),(3,'Joe Blogs')
    
    Create Table #forum2 (KeywordId int, UserId int, Keyword varchar(20))
    insert into #forum2 values (1,1,'Brown Hair'),(2,1,'Blue Eyes'),(3,2,'Brown Hair'),(4,2,'Brown Eyes'),(5,3,'Blonde Hair'),(6,3,'Brown Eyes'),(7,4,'Blonde Hair'),(8,4,'Blue Eyes')
    
    --Solution 1
    Select * from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where t1.userid=
    (
    Select t1.Userid from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where Keyword in ('brown hair', 'blue eyes')
    group by t1.UserId having count(KeywordId)>1)
    
    --Solution 2
    with ct as (
    Select t1.Userid from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where Keyword in ('brown hair', 'blue eyes')
    group by t1.UserId having count(KeywordId)>1)
    select * from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId inner join ct c on t1.userid=c.userid
    
    --Solution 3
    With ct as (
    Select t1.Userid, Count(Keyword) over (partition by t2.userid) rc from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where Keyword in ('brown hair') or  Keyword in ('blue eyes')
    )
    select name, keyword  from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId inner join ct c on t1.userid=c.userid
    where rc>1
    group by name, keyword 

    Please mark as answer if this post helped you


    Wednesday, February 27, 2019 11:41 AM
  • I think you meant something like this?

    declare @users table
    (
    
    userID int,
    [Name] varchar(50)
    )
    
    insert @users
    values
    
    (1,'John Doe'),
    
    (2,'Jane Doe'),
    
    (3,'Joe Blogs'),
    
    (4,'Mark Wales'),
    
    (5,'Jimmy Bolt'),
    
    (6,'Michelle Brad')
    
    declare @keywords table
    (
    KeywordID int,
    UserID int,
    Keyword  varchar(50)
    )      
    
    insert @keywords
    values 
    (1, 1,'Brown Hair'),  
    
    (2,1,'Blue Eyes'),     
    
    (3,2,'Brown Hair'),     
    
    (4,2,'Brown Eyes'),     
    
    (5,3,'Blonde Hair'),     
    
    (6,3,'Brown Eyes'),
    
    (7,4,'Blonde Hair'),     
    
    (8,4,'Blue Eyes'),
    
    (7,5,'Blonde Hair'),     
    
    (8,5,'Brown Eyes'),
    
    (7,6,'Blue Eyes'),     
    
    (8,6,'Brown Hair')
    
    
    ;with userkeywords
    as
    (
    select u.Name,
    stuff((select ',' + keyword
    from  @keywords 
    where userid = u.userid
    order by keyword
    for xml path('')),1,1,'') AS keywordlist
    from @users u
    
    ),keywordgrp
    as
    (
    select keywordlist,
    stuff((select ',' + Name
    from userkeywords
    where keywordlist = u.keywordlist
    for xml path('')),1,1,'') as userlist
    from userkeywords u
    group by keywordlist
    )
    
    select *
    from keywordgrp
    
    
    
    
    /*
    
    Output
    ---------------------------------------------
    keywordlist	        userlist
    ----------------------------------------------
    Blonde Hair,Blue Eyes	Mark Wales
    Blonde Hair,Brown Eyes	Joe Blogs,Jimmy Bolt
    Blue Eyes,Brown Hair	John Doe,Michelle Brad
    Brown Eyes,Brown Hair	Jane Doe
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 27, 2019 12:04 PM

All replies

  • Please use the query below

    DECLARE @Table1 table (UserId INT, Name VARCHAR(25))
    
    INSERT INTO @Table1
    SELECT 1, 'John Doe' union all
    SELECT 2, 'Jane Doe' union all 
    SELECT 3, 'Joe Blogs'
    
    DECLARE @Table2 TABLE (KeywordID INT, UserID INT, Keyword NVARCHAR(35))
    
    INSERT INTO @Table2
    SELECT 1,  1, 'Brown Hair' union all     
    SELECT 2,  1, 'Blue Eyes'  union all   
    SELECT 3,  2, 'Brown Hair' union all
    SELECT 4,  2, 'Brown Eyes' union all
    SELECT 5,  3, 'Blonde Hair' union all  
    SELECT 6,  3, 'Brown Eyes' union all
    SELECT 7,  4, 'Blonde Hair' union all
    SELECT 8,  4, 'Blue Eyes'
    
    
    SELECT NAME
    FROM @Table1
    WHERE UserId IN (
    		SELECT a.userid
    		FROM @table1 a
    		INNER JOIN @Table2 b ON a.UserId = b.UserID
    		WHERE b.Keyword IN (
    				'Brown Hair'
    				,'Blue Eyes'
    				)
    		GROUP BY a.UserId
    		HAVING count(DISTINCT keywordId) = 2
    		)
    
    

    Results:

    John Doe

    Regards, Dineshkumar,
    Please Mark as Answer if my post answers your question and Vote as Helpful if it helps you

    DWBI Blog


    Wednesday, February 27, 2019 11:25 AM
  • Do you mean how can you select this structure?

    Like:

    Create table #forum1 (userid int, name varchar(20))
    insert into #forum1 values (1,'John Doe'),(2,'Jane Doe'),(3,'Joe Blogs')
    
    Create Table #forum2 (KeywordId int, UserId int, Keyword varchar(20))
    insert into #forum2 values (1,1,'Brown Hair'),(2,1,'Blue Eyes'),(3,2,'Brown Hair'),(4,2,'Brown Eyes'),(5,3,'Blonde Hair'),(6,3,'Brown Eyes'),(7,4,'Blonde Hair'),(8,4,'Blue Eyes')
    
    --Solution 1
    Select * from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where t1.userid=
    (
    Select t1.Userid from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where Keyword in ('brown hair', 'blue eyes')
    group by t1.UserId having count(KeywordId)>1)
    
    --Solution 2
    with ct as (
    Select t1.Userid from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where Keyword in ('brown hair', 'blue eyes')
    group by t1.UserId having count(KeywordId)>1)
    select * from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId inner join ct c on t1.userid=c.userid
    
    --Solution 3
    With ct as (
    Select t1.Userid, Count(Keyword) over (partition by t2.userid) rc from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId 
    where Keyword in ('brown hair') or  Keyword in ('blue eyes')
    )
    select name, keyword  from #forum1 t1 inner join #forum2 t2 on t1.Userid=t2.UserId inner join ct c on t1.userid=c.userid
    where rc>1
    group by name, keyword 

    Please mark as answer if this post helped you


    Wednesday, February 27, 2019 11:41 AM
  • I think you meant something like this?

    declare @users table
    (
    
    userID int,
    [Name] varchar(50)
    )
    
    insert @users
    values
    
    (1,'John Doe'),
    
    (2,'Jane Doe'),
    
    (3,'Joe Blogs'),
    
    (4,'Mark Wales'),
    
    (5,'Jimmy Bolt'),
    
    (6,'Michelle Brad')
    
    declare @keywords table
    (
    KeywordID int,
    UserID int,
    Keyword  varchar(50)
    )      
    
    insert @keywords
    values 
    (1, 1,'Brown Hair'),  
    
    (2,1,'Blue Eyes'),     
    
    (3,2,'Brown Hair'),     
    
    (4,2,'Brown Eyes'),     
    
    (5,3,'Blonde Hair'),     
    
    (6,3,'Brown Eyes'),
    
    (7,4,'Blonde Hair'),     
    
    (8,4,'Blue Eyes'),
    
    (7,5,'Blonde Hair'),     
    
    (8,5,'Brown Eyes'),
    
    (7,6,'Blue Eyes'),     
    
    (8,6,'Brown Hair')
    
    
    ;with userkeywords
    as
    (
    select u.Name,
    stuff((select ',' + keyword
    from  @keywords 
    where userid = u.userid
    order by keyword
    for xml path('')),1,1,'') AS keywordlist
    from @users u
    
    ),keywordgrp
    as
    (
    select keywordlist,
    stuff((select ',' + Name
    from userkeywords
    where keywordlist = u.keywordlist
    for xml path('')),1,1,'') as userlist
    from userkeywords u
    group by keywordlist
    )
    
    select *
    from keywordgrp
    
    
    
    
    /*
    
    Output
    ---------------------------------------------
    keywordlist	        userlist
    ----------------------------------------------
    Blonde Hair,Blue Eyes	Mark Wales
    Blonde Hair,Brown Eyes	Joe Blogs,Jimmy Bolt
    Blue Eyes,Brown Hair	John Doe,Michelle Brad
    Brown Eyes,Brown Hair	Jane Doe
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 27, 2019 12:04 PM