SQL query with multiple same conditions

I work with Sybase DB and trying to select data using few conditions:

The table name “alias”.

a_id, a_data, a_type_id, a_value
------------------------------
    1,   666,       123, "value_1"
    2,   666,       456, "value_2"
    3,   777,       123, "value_4"
    4,   777,       456, "value_5"

I have few conditions:

1. a_type_id = 123 and a_value = "value_1" AND
2. a_type_id = 456 and a_value = "value_2"

So the result should be a_data = 666, in case the both conditions are achived. The query should be like this, but it, of course, does not work, returns 0 results:

select a_data from alias where ( a_type_id = 123 AND a_value = "value_1" ) AND (a_type_id = 456 AND a_value = "value_2")

Expecting result a_data = 666.

Can someone advise something workaround? It is possible to have 2, 3 or more conditions like above.

Answer

Use aggregation and having:

select a_data
from t
where (a_type_id = 123 and a_value = 'value_1') or
      (a_type_id = 456 and a_value = 'value_2')
group by a_data
having count(*) = 2;

Note: This assumes that type/value pairs are not duplicated in the original data for a given a_data value. If they can be, then use:

having count(distinct a_type_id) = 2

Advertisement

Answer