Null values in a join 1

[robman70]

I'm having problems with a join again, my sql statement has an inner join:

select table1.name, table2.user, table3.dest from table1 INNER JOIN table2 on table2.user = table1.uname INNER JOIN table3 on table3.dest = table1.destid

what i want to know is how can i say that sometimes table2.user will be null and if it is just match the other two conditions and return null for table2.user. I hope im being clear i know it may sound a little confusing. i tried:

select table1.name, table2.user, table3.dest from table1 INNER JOIN table2 on table2.user = table1.uname or is null table2.user INNER JOIN table3 on table3.dest = table1.destid

but it didnt like that

any help appreciated

 

[SQLSister]

Give us asample of your data inthe two tables and what result you want. Nulls are impossible in a jopin becasue what would they join too? However if what you are trying to do is find the records in table1 which are not in table2 that is possible with a left join.

Questions about posting. See faq183-874

 

[robman70]

Thanks sqlsister, left join was exactly what i needed

 

[ESquared]

For what it's worth, you can join columns that have Null values.

You might be able to do it with

SET ANSI_NULLS OFF

And you can definitely do it with

ON IsNull(A.Key,'uniquevalue') = IsNull(B.Key,'uniquevalue')

but these are just hacks and probably shouldn't be used for anything important.


-------------------------------------
A sacrifice is harder when no one knows you've made it.