SQL pagination using INNER JOINs and filtering with LIKE

Question

This query feeds a data table with sorting, filtering, and pagination. All features worked fine until I added the INNER JOIN and then i got:

The multi-part 'identifier "Types.Description" could not be bound

if i remove the second WHERE clause at the end of the query the LIKE statements work, but i lose pagination. I removed some of the LIKE clauses to try and clean up this monstrous query.

SELECT * 
 FROM ( 
    SELECT ROW_NUMBER() OVER (ORDER BY  TAG asc) AS RowNumber, * 
    FROM (
      SELECT (SELECT COUNT(*) FROM Instruments) AS TotalDisplayRows, (SELECT COUNT(*) FROM Instruments) AS TotalRows, Instruments.Tag, Instruments.Location, Instruments.Description, Types.Description As TypeDesc, Manufacturer.Name, Lease.Name as LeaseName, Facility.Name as FacName 
      FROM Instruments 
      INNER JOIN Types ON Instruments.Type = Types.ID 
      INNER JOIN Manufacturer ON Instruments.Manufacturer = Manufacturer.ID 
      INNER JOIN Facility ON Instruments.Facility = Facility.ID 
      INNER JOIN Lease ON Instruments.Lease = Lease.ID 
      WHERE (Types.Description LIKE '%Cat%')
    ) RawResults
  ) Results 
  WHERE (Types.Description LIKE '%Cat%') AND RowNumber BETWEEN 1 AND 10

Answer 1

I think this is your problem

WHERE (types.description LIKE '%Cat%') 

You can't do this because you are actually selecting from your derived table named Results and you aliased the column as TypeDesc.

So it should be

WHERE (results.typeDesc LIKE '%Cat%')