CODEWITHSUNDEEP
sqlsql-servert-sql
Thang Nguyen
Thang Nguyen

Reputation: 23

TSQL - How to get a record satisfies a condition without excluding others that do not

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)

Upvotes: 0

Views: 122

Answers (5)

user10679624
user10679624

Reputation: 

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 0

plalx
plalx

Reputation: 43718

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.

Upvotes: 2

Eric Brandt
Eric Brandt

Reputation: 8101

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 |
+--------+-------+

Upvotes: 1

SteveB
SteveB

Reputation: 799

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 ;

Upvotes: 0

Related Questions