t-sql query to get all the rows from Table_A which have at least one matching relatedid in Table_B

Based on the below tables

Table_A

Id  RelatedId
---------------
1   1
1   2
1   3
2   4
2   5
2   2
3   7
3   8
4   9

Table_B

RelatedId  Name
--------------
2          A
3          B

I want to get all the rows from Table_A which have at least one matching relatedid in Table_B. The Ids from Table_A that do not have match in Table_B will have single row in the result table.

So output (result table) in this case will be

Id  RelatedId
---------------
1   1
1   2
1   3
2   4
2   5
2   2
3   Null
4   Null

EDIT: Seems like the question text is confusing for many. So a detailed explanation:

Table_A Id 1 has both 2 and 3 (Related Ids) matching in table_B. So output will have all the rows for 1 from Table_A. Similary I开发者_Go百科d 2 from Table_A has 2 (Related Id) matching in table_B. So all rows corresponding to 2 from Table_A will be picked up. Since 3 does not have any matching relatedid in table_B it will be displayed but with NULL as relatedid in the results table.

---

with validids as (
 select distinct id 
 from Table_A 
  inner join Table_B on 
   Table_A.relatedid = Table_B.relatedid
)
select 
  id,
  relatedid
from Table_A 
where id in (select id from validids)
union 
select distinct
 id, 
 null
from Table_A
where id not in (select id from validids)

---

Try:

  Select Distinct Id, Related_Id
  From Table_A 
  Where Related_Id In 
      (Select Related_Id From Table_B) 

---

This should do what you want:

with IdsWithMatchInB(Id) as (
  select distinct
    Id
  from Table_A
  where Table_A.RelatedId in (
    select Table_B.RelatedId
    from Table_B
  )
)
  select
    Table_A.Id,
    CASE WHEN IdsWithMatchInB.Id IS NULL
         THEN NULL
    ELSE Table_A.RelatedId END AS RelatedId
from Table_A
left outer join IdsWithMatchInB
on IdsWithMatchInB.Id = Table_A.Id

Here's another option that avoids DISTINCT by putting the subquery inside the CASE expression:

select
  Id,
  case when Id in (
    select Id
    from Table_A as Acopy
    where Acopy.RelatedId in (
      select
        RelatedId 
      from Table_B
    )
  )
  then RelatedId
  else null end as RelatedId
from Table_A;

One or the other might be more efficient for your particular data and indexes.