Htykuut

It seems pretty simple but I have not touched SQL for years. When I come back , totally stuck with this math. Let say I have 3 tables as below:

Table Name:

|NameId| Name |

|------|------|

| 1    | John |

| 2    | Doe  |

| 3    | Brian|

Table Tag:

|TagId| Tag     |

|-----|---------|

| 1   | Teacher |

| 2   | Engineer|

| 3   | Employee|

Table NameTag:

|NameId|TagId|

|----  |-----|

|1     | 1   |

|2     | 2   |

|2     | 3   |

|3     | 3   |

I want to find all names along with associated tags by a tag. E.g. find names with tag Employee (TagId = 3). I expect result like this:

|NameId|TagId|

|----  |-----|    

|2     | 2   |

|2     | 3   |

|3     | 3   |

How can I achieve that in a T-SQL script?

I tried with script below but it always excludes the records 1|1 and 2|2:

SELECT *

FROM NameTag    

WHERE TagId = 3

Result I do not expect:

|NameId|TagId|

|----  |-----|        

|2     | 3   |

|3     | 3   |

UPDATE

Looks like there are many solutions commented below as @plax, @SteveB or @picklerick and some others pointed. I have not had a chance to test all of them but they seem working for me. Thank you all! (early thanksgiving today for me).

By that time, I have also found a solution myself and posted here for reference. Also, @plax pointed out below.

My solution:

--Get persons that have the tag.

WITH Person_CTE AS

(

    SELECT DISTINCT NameId 

    FROM NameTag

    WHERE TagId = 3

)

-- Looking other tags for the person.

SELECT * 

FROM NameTag nt

JOIN Person_CTE p ON nt.NameId = p.NameId

Another solution as @plalx has pointed out that was written very clearly:

1. 
   

   Find all NameId that have an associated TagId of 3. E.g.

   
   
   

   SELECT NameId
   
   FROM NameTag
   
   WHERE TagId = 3
   
   

   
   


2. 
   

   Find all tags for NameId we found in step #1.

   
   
   

   SELECT NameId, TagId
   
   FROM NameTag
   
   WHERE NameId IN 
   
       (-- Solution from #1
   
        SELECT NameId
   
        FROM NameTag
   
        WHERE TagId = 3)
   
   

   
   

When you can't work out a query problem, first break it down into smaller problems you can solve and then merge the smaller solutions together.

1. 
   

   Find all NameId that have an associated TagId of 3. E.g.

   
   
   

   SELECT NameId
   
   FROM NameTag
   
   WHERE TagId = 3
   
   

   
   


2. 
   

   Find all tags for NameId we found in step #1.

   
   
   

   SELECT NameId, TagId
   
   FROM NameTag
   
   WHERE NameId IN (
   
       -- Solution from #1
   
       SELECT NameId
   
       FROM NameTag
   
       WHERE TagId = 3
   
   ) 
   
   

   
   

If you then run into performance issues you can start from there and try to optimize, but the expressiveness of the query is also important for maintainability.

Or with a correlated subquery instead of a join, just as another way to skin that cat.

SELECT

  *

FROM

  @NameTag AS nt

WHERE

  EXISTS

(

  SELECT

    1

  FROM

    @NameTag AS n

  WHERE

    n.TagId = 3

    AND n.NameId = nt.NameId

);

Results:

+--------+-------+

| NameId | TagId |

+--------+-------+

|      2 |     2 |

|      2 |     3 |

|      3 |     3 |

+--------+-------+

Select * from nametag where nameid in(Select nameid from nametag where tagid=3 Group by nameid) 

I think this might be what you are looking for.

DECLARE @NameTag TABLE (NameId int, TagId int)



INSERT INTO @NameTag SELECT 1, 1

INSERT INTO @NameTag SELECT 2, 2

INSERT INTO @NameTag SELECT 2, 3

INSERT INTO @NameTag SELECT 3, 3



SELECT a2.*

FROM @NameTag a1

    JOIN @NameTag a2 on a1.NameId = a2.NameId

WHERE a1.TagId = 3 ;

You can do this without a join:

select nt.*

from (select nt.*,

             sum(case when tagid = 3 then 1 else 0 end) over (partition by nameid) as cnt_3

      from nametag nt

     ) nt

where cnt_3 > 0;