CODEWITHSUNDEEP
sqloracle-database
SüniÚr
SüniÚr

Reputation: 896

Get 1 if statments are true

I am trying to get some id-s from a join on multiple conditions. Example After join etc.

A B
x 1
x 2
x 3
y 1
y 2
y 3
y 5
z 1
z 5

so i want those id-s ( A) where for example B is 1 and also 5

so the result y,z

Thanks

Upvotes: 0

Views: 81

Answers (4)

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

If the conditions are "distinct" in the sense that a given row can only satisfy exactly one of the conditions, then something like this can be used:

select a
  from table1
 group by a
having count(
   case
      when b = 1 then 1
      when b = 5 then 1
   end
) = 2
 order by a

Set up each condition in the case structure and test if the count is equal to the number of conditions.

Alternatively a method similar to the one by Egor Skriptunoff:

select a
  from table1
 group by a
having max(case when b=1 then 1 end)=1
   and max(case when b=5 then 1 end)=1
 order by a

A max() on each condition.

Both methods do not require self-joins.

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

select A from Table group by A
having max(decode(B,1,0)) + max(decode(B,5,0)) = 0

fiddle

Upvotes: 1

D Stanley
D Stanley

Reputation: 152556

Using two EXISTS may look cleaner : 

SELECT DISTINCT A
FROM Table1 t1
WHERE EXISTS(SELECT A FROM Table1 WHERE A = t1.A and B = 1)
  AND EXISTS(SELECT A FROM Table1 WHERE A = t1.A and B = 5)

Upvotes: 3

T McKeown
T McKeown

Reputation: 12847

You can achieve this by a WHERE and a GROUP + HAVING, the HAVING will ensure that 2 conditions are met. 

SELECT T1.A
FROM Table1 AS T1
JOIN Table2 AS T2
  ON T2.ID = T1.ID
WHERE T2.B IN(1,5)
GROUP BY T1.A
HAVING COUNT(*) = 2

Or if there is a possibility of B having more than one row for 1 or 5 for the same A, you could do this:

SELECT DISTINCT T1.A
FROM Table1 AS T1
JOIN Table2 AS T2
  ON T2.ID = T1.ID
 AND T2.B = 1  
JOIN Table2 AS T3
  ON T3.ID = T1.ID
 AND T3.B = 5

Upvotes: 1

Related Questions