Sql错误说“语法不正确”=“

Question

In this sql I just want to check in "ON" clause b.block='False'.Because sometime "profileview " table values are empty. I just want ,If user_fullinfo values matches in profileview with this condition b.block='False' It should be display,If values are not there in profile view then also it should be display,

SELECT  a.* FROM user_fullinfo a left join profileview b on
CASE
WHEN a.id=b.rid and b.rid='105' THEN 
b.block='False' 
END
where gender != 'Male' and (DATEDIFF(YY,dob,GETDATE()) Between '10' and '100') and (a.heightid between 58 and 65) and( a.complexionid = '3' or a.complexionid = '4' or a.complexionid = '5') and a.bodytypeid = '4' and a.marital_statusid = '3'

it says sql error (102) :incorrect syntax near "=".. Please give solution..

Answer 1

Boolean expressions are not suitable as values, such as the value for a case. My advice is to remove the case entirely and just use boolean logic. Something like this:

select a.*
from user_fullinfo a left join
     profileview b
     on (a.id = b.rid and b.rid = '105' and b.block = 'False') or 
        ( not (a.id = b.rid and b.rid = '105' and b.block = 'False') and
          gender <> 'Male' and (datediff(year, dob, getdate()) Between 10 and 100) and
          (a.heightid between 58 and 65) and
          (a.complexionid in (3, 4, 5)) and
          a.bodytypeid = 4 and a.marital_statusid = 3
        );

Notes:

• The standard for SQL inequality in SQL is <>, although != is also widely accepted.
• Don't use single quotes around numeric constants. between '10' and '100' is not the same as between 10 and 100.
• I doubt that this version or your case version really does what you want. There is no correlated between the two tables for the second condition.

This should solve your syntax problems. The logical problem is a different issue. I would suggest that you ask another question, with sample data, desired, results, and an explanation of the logic.