Condition on two fields

I have a SQL Select and I am not sure how I can achieve this. I am checking two fields to see if any of those fields are in a list. So like,

开发者_开发百科

Select * from MyTable where col1 or col2 in (select col3 from OtherTable where ID=1)

I tried

Select * from MyTable where 
col1  in (select col3 from OtherTable where ID=1)
or col2 in (select col3 from OtherTable where ID=1)

But, this returns the records that match first condition (only returns col1, but not col2) for some reasons.

---

Try this -

Select * from MyTable where 
(col1  in (select col3 from OtherTable where ID=1))
or 
(col2 in (select col3 from OtherTable where ID=1) )

---

if you're subquery is the same for both columns, i'd throw it into a cte, then do a left outer join on the cte on col1 and col2, then do your where statement.

;with c3 as
(
    select col3
    from OtherTable
    where ID=1
)

select m.*
from MyTable m
    left outer join c3 as c1
        on m.col1=c1.col3
    left outer join c3 as c2
        on m.col2=c2.col3
where 
    (c1.col3>'')
    or (c2.col3>'')

if a blank varchar that isn't null is a viable option, change your where clauses to >=.

---

SELECT t.*
FROM MyTable t
  INNER JOIN (
    select col3
    from OtherTable
    where ID=1
  ) sel ON sel.col3 IN (t.col1, t.col2)