left outer join - question
-
Hello, I have two tables, A like number, value1 value2, and B like number, aktiv, value2, ... As I want to have all lines of table A, even number does not exist in table B I made a command 'select from A left outer join B on A.number = B.number' Works fine, but if I try "where B.aktiv != '-1'", the NULL-Values also disapears. I also tried "(where B.aktiv = '1') or (B.aktiv = NULL)" - it doesn't work. What am I doing wrong? Thanks, Gerhard
-
Hello, I have two tables, A like number, value1 value2, and B like number, aktiv, value2, ... As I want to have all lines of table A, even number does not exist in table B I made a command 'select from A left outer join B on A.number = B.number' Works fine, but if I try "where B.aktiv != '-1'", the NULL-Values also disapears. I also tried "(where B.aktiv = '1') or (B.aktiv = NULL)" - it doesn't work. What am I doing wrong? Thanks, Gerhard
NULL cannot be compared to any value, so it is neither = -1 of != -1 or even =Null. If you need to find NULL values you must use IS NULL (Where B.aktiv IS NULL) NOTE: If you turn the ANSI_NULLS option off, Transact SQL will return comparisons for null, SEE Books Online for more info.
-
Hello, I have two tables, A like number, value1 value2, and B like number, aktiv, value2, ... As I want to have all lines of table A, even number does not exist in table B I made a command 'select from A left outer join B on A.number = B.number' Works fine, but if I try "where B.aktiv != '-1'", the NULL-Values also disapears. I also tried "(where B.aktiv = '1') or (B.aktiv = NULL)" - it doesn't work. What am I doing wrong? Thanks, Gerhard
It seems that columns having NULL values in B.aktiv is getting eliminated. You can try the following: - WHERE ISNULL(B.aktiv,1)!='-1' Let me know if its working for you.