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

Question

Hi all,

Assume I have the following style table, 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.

Answer 1

Not idea but here is a quick example. (TSQL)

SELECT id, col1, col2, col3
FROM <table>
WHERE (col1 IN ('ab','bc') AND col2 IN ('ab','bc')) OR
      (col2 IN ('ab','bc') AND col3 IN ('ab','bc')) OR
      (col1 IN ('ab','bc') AND col3 IN ('ab','bc'))

Answer 2

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")