how to select the records whose several fields' combination will equal to a specific value

Assume I have the following style table开发者_JS百科, col1 col2 and col3 have same value scopes, I want to select the records when two of the 3 columns have a value combination such as ('ab' and 'bc'), in the following example, the first 3 records should be selected. Any good way to do this? I am using Sybase.

| id | col1 | col2 | col3 |
  1     ab     bc     null
  2     null   ab     bc
  3     ab     ab     bc
  4     de     ab     xy

Thanks.

---

I don't have Sybase to check, but you can Try this:

select * from Table where (col1 = "ab" or col2 = "ab" or Col3 = "ab") 
and (col1 = "bc" or col2 = "bc" or Col3 = "bc")

---

I agree that the answer given here is totally acceptable, however I feel that if there were more than a few columns that needed to be evaluated, nesting the comparisons in the WHERE clause could become a bit cumbersome and illegible. I had to overcome a similar problem and I found that the following technique was quite helpful (I have adapted it to solve the issue listed above). Note that the evaluation of Total in the outer query can be easily adapted to increase or decrease the number of columns that contain the criteria being evaluated:

SELECT *
FROM
(SELECT id
      , col1          
      , col2
      , col3
      , SUM(CASE WHEN UPPER(col1) IN ('AB', 'BC') THEN 1 ELSE 0 END 
          + CASE WHEN UPPER(col2) IN ('AB', 'BC') THEN 1 ELSE 0 END
          + CASE WHEN UPPER(col3) IN ('AB', 'BC') THEN 1 ELSE 0 END) as Total
FROM <table>
GROUP BY id 
       , col1
       , col2
       , col3) as results
WHERE Total >= 2
ORDER BY id