SQL Select results based on two rows with different condition

I’m trying to fetch results where I want to check if two rows exist for a common ForeignKeyId and add a check on both rows.

Suppose I have a table below – ‘Test1’

A    B    C    D                               'Test2'
1    33   x    NULL                             Result       Id
2    33   NULL y                                Result1      33
3    44   x    NULL                             Result2      44
4    44   NULL z                                Result3      55
5    55   x    NULL

I want to fetch Result from Test2 table where Test1 should contain two rows where C = x and D <> NULL.

So, select query should return Result1 and Result2, not Result3

I tried:

select Result
from Test2
inner join Test1 on Test2.Id = Test1.A
where 
    (select Count(*) from Test1 where C = 'x') = 1 AND 
    (select Count(*) from Test1 where D IS NOT NULL) = 1;

Answer

To compare the same value in a different row a self join is used. In this case There are two Test1 tables, the first (Tc) matches the C criteria, and the second Td matches the D criteria.

SELECT Result,Tc.B
FROM Test1 Tc
JOIN Test1 Td
  ON Tc.B=Td.B
JOIN Test2
  ON Td.B=Test2.Id
WHERE
   Tc.C = 'x'
   AND Td.D IS NOT NULL