Using joined table column in where clause gives error

1

I am trying to use a joined table column that happens to also be aliased in a where clause.

My query is slightly more complex than this but here is the essence:

SELECT a.Name, b.Name AS xName 
FROM TableA a 
LEFT JOIN TableB b ON a.id=b.aid 
WHERE a.Name LIKE @Filter OR b.Name LIKE @Filter

Throws error: The multi-part identifier 'b.Name' could not be bound

EDIT:

Resolved. I would delete this question but I do not know how to do that.

The error was not thrown by this query but by one based on it to get the total record count for use in a paged table.

So the query throwing the error was:

SELECT COUNT(*) AS total 
FROM TableA a 
WHERE a.Name LIKE @FilterValue OR b.Name LIKE @FilterValue

Which of course won't work.

But this does:

SELECT COUNT(*) AS total 
FROM TableA a 
LEFT JOIN TableB b ON a.id=b.aid 
WHERE a.Name LIKE @FilterValue OR b.Name LIKE @FilterValue
1 Like
2

Just a little note:
Using left (outer) join on tableb indicates you want rows from tablea even if the rows in tableb doesn't match the join. But when you then filter a column from tableb, for non-null value in the where section, this would actually turn you left (outer) join into inner join.

:+1: for not deleting

1 Like
3

Good point, thanks. 99.9% of the time I use a LEFT OUTER JOIN because I want rows from the 'main' table no matter what, did not even think about the implications this has for a WHERE clause.

1 Like