I'm joining two tables, where second table consist of bit data type, which I was hoping to filter by, but cannot figure out correct syntax.
SELECT *
FROM [Table1]
JOIN [Table2] on [Table2].ID = [Table1].ID
WHERE [Table2].Column1 = 'test'
AND [Table2].Column2 = 1
Column1 is varchar, Column2 is bit,null. It fails on Column2.
What part is failing? If Column2 is NULL then, then Column2 = 1 will return false and the row won't be returned.
As I said it fails on column2 and says incorrect syntax near column2.
Ah, yes you did say "syntax". Hmmm. That looks right to me. BIT should be 1 or 0 in a WHERE clause or assignment clause. Unless it's a typo or I'm missing something obvious I'm not sure what it is.
BIT is not an INTEGER, and to be precise it can be 1, 0 or null, INTEGER cannot be null. So BIT has to be treated differently, but I'm not sure what is correct syntax here.
Try this
Coalesce( [Table2].Column2,-1) = 1
That is proper syntax. The syntax error has to be somewhere else. Often a missing "END" statement or other syntax error isn't caught by the parser until later in the SQL. O the column name is a reserved word, bu that should give a more specific syntax error.
Btw, since those conditions must be true in order to JOIN, you could add them to the ON clause instead of using WHERE:
SELECT *
FROM [Table1]
JOIN [Table2] on [Table2].ID = [Table1].ID AND [Table2].Column1 = 'test' AND [Table2].Column2 = 1
None of the suggestions above worked 
Here is actual query. If BIT is being treated same as INT then I cannot see how this could fail.
SORTED. Square brackets around [View] helped.
Thanks everyone for efforts.
Yes because the word view is a reserved word, which you never told us about. glad you sorted it out.
I was sure it was down to BIT type not being equal to INT.
