CODEWITHSUNDEEP
sqlsql-server
Gaurav Gupta
Gaurav Gupta

Reputation: 139

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;

Upvotes: 3

Views: 4796

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest using exists:

select t2.*
from test2 t2
where exists (select 1 from test1 t1 where t2.id = t1.b and t1.c = 'x') and
      exists (select 1 from test1 t1 where t2.id = t1.b and t1.d is not null);

The reason for this is that you will not get duplicates if multiple rows match either condition in table1.

Note: The above could return return with only one matching row in table1 if both conditions are true for that row. You can require two rows by having more precise conditions:

where exists (select 1 from test1 t1 where t2.id = t1.b and t1.c = 'x' and t1.id is null) and
      exists (select 1 from test1 t1 where t2.id = t1.b and t1.d is not null and (t1.c <> 'x' or t1.c is null));

Upvotes: 1

danblack
danblack

Reputation: 14666

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

Upvotes: 3

Related Questions