if conditions inside where sql-CodePudding

I'm trying this logic not sure what am missing in this case

select * 
  from table1 t1 
       join table2 t2 on t1.column1=t2.column1
 where t1.column1 between 1 and 10 if t2.column2='value1' 
   and t1.column1 between 11 and 20 if t2.column2='value2'

CodePudding user response：

You could use a case when aproach

CREATE tABLE table2(column1 int, column2 varchar(10))

INSERT INTO table2 VALUEs(1,'value1'),(2,'value1'),(13,'value2'),(44,'value2')

CREATE tABLE table1(column1 int)

INSERT INTO table1 VALUES (1),(2),(13),(44)

select * 
  from table1 t1 
       join table2 t2 on t1.column1=t2.column1
 where 
 CASE WHEN t1.column1 between 1 and 10 AND t2.column2 like 'value1' THEN TRUE
  WHEN t1.column1 between 11 and 20 AND t2.column2 like 'value2' THEN TRUE
 ELSE FALSE END

column1 | column1 | column2
------: | ------: | :------
      1 |       1 | value1 
      2 |       2 | value1 
     13 |      13 | value2

db<>fiddle here

CodePudding user response：

I assume you've left out key details, like the ON for the JOIN and the wildcard for the LIKE, in the interest of simplicity.

select * 
  from table1 t1 
       join table2 t2 
 where (t1.column1 between 1 and 10 and t2.column2 like 'value1')
   or (t1.column1 between 11 and 20 and t2.column2 like 'value2')