query same value in a column in different in other tow

Question

I have a table COND with:

ORDERNO    NAME    OUTPUT SIGN

12         HOUSE    O     +
13         CAR      O     -
14         BOOK     I     +
13         HOUSE    I     A

I need to get the name of the rows that have: Same name and two rows: one with OUTPUT = O and SIGN = + another row with OUTPUT= I and SIGN = A.

In these example only should get: HOUSE

Only Value HOUSE. These value is the correct

Answer 1

We can use conditional aggregation here:

SELECT NAME
FROM yourTable
GROUP BY NAME
HAVING COUNT(CASE WHEN OUTPUT = 'O' and SIGN = '+' THEN 1 END) > 0 AND
       COUNT(CASE WHEN OUTPUT = 'I' and SIGN = 'A' THEN 1 END) > 0;