Getting "no join predicate" warning even tho I am using "inner join"

Question

I am using SQL server 2008 R2 and I have a query that inner joins 3 tables:

select * from  t1 INNER JOIN t2 on t1."Account Key" = t2."Account Key"
INNER JOIN t3 on t2."Account Key" = t3."Account Key"

The problem is that I am getting a "no join predicate" warning on the second inner join; in addition to that, it is doing cross product of the table produced by the 1st join with t3 which makes the query run forever (besides giving the wrong results).

I ran the following query to see if there are any account keys equal on both sides of the last join:

select "Account Key" from  t1 INNER JOIN t2 on t1."Account Key" = t2."Account Key"
INTERSECT 
select "Account Key" from t3 on t2."Account Key" = t3."Account Key"

I get 0 rows, which means that there are no account keys equal on on both sides of the join. However, should not the original query give 0 rows instead of giving a cross product since I am using an "inner join"?

Update: The t3 is acutally a view whose [Account Key] column is actually a calculated column:

CAST(bid AS varchar(5)) + '-' + dbo.ConvertNumericToPaddedChar(RMKEY, 20) AS [Account Key]

Update: the dbo.ConvertNumericToPaddedChar(RMKEY, 20) is what causing the problem. It seems that when doing inner joins, SQL Server can't use columns that were built using user-defined functions.

Do any of you know any way around this?

Answer 1

I'm not sure why your example wouldn't work. Try surrounding your column names with [brackets] instead of "quotes".

select * from  t1
INNER JOIN t2 on t1.[Account Key] = t2.[Account Key]
INNER JOIN t3 on t2.[Account Key] = t3.[Account Key]