Inner join problem

Question

I am hoping someone can point out where I am going wrong with this because I have spent quite some time trying to resolve this by googling but getting nowhere.  I am writing a select query to create a report which pulls information from numerous linked tables.  I am only using Inner Join to link the tables together.  All of the joins work fine until I add my final Inner Join to pull a description from a lookup table.  When I add that join in I get an Invalid Object Name error for tbLookup.  The table is definitely correctly spelled and the primary and foreign keys are definitely correct because I have tested them in a separate simpler query, which works.  If I take tbLookup table out of the script then it runs correctly.  Any help would be much appreciated  The script is below:

SELECT bu.BusinessUnitName AS [Business Unit Name]
, l.LeaseTitle
, cs.NetAmount
, pp.Description AS [Payment Period]
, RTrim(Coalesce(ct.FirstName + ' ','')+ + Coalesce(ct.LastName + ' ', '')) As Fullname
, cs.RangeStartDate
, cs.RangeEndByDate
, lu.Description AS [Payment Term]
, av.Value AS [Profit Centre]
, av1.Value AS [Sales Office]
FROM tbBusinessUnit AS bu INNER JOIN
tbBuildingLinkBusinessUnit AS blbu ON bu.BusinessUnitID = blbu.BusinessUnitID INNER JOIN
tbBuilding AS bl ON blbu.BuildingID = bl.BuildingID INNER JOIN
tbUnitLocation AS ul ON bl.BuildingID = ul.LocationID INNER JOIN
tbUnit AS u ON ul.UnitID = u.UnitID INNER JOIN
tbLease AS l ON u.UnitID = l.UnitID INNER JOIN
tbChargeSchedule AS cs ON l.LeaseUID = cs.ParentID INNER JOIN
tbPaymentPeriod AS pp ON cs.PaymentPeriodID = pp.PaymentPeriodID INNER JOIN
tbLeaseParty AS lp ON l.LeaseUID = lp.LeaseUID INNER JOIN
tbContactLink AS cl ON lp.LeasePartyID = cl.LocationID INNER JOIN
tbContact AS ct ON cl.ContactUID = ct.ContactUID INNER JOIN
tbAttributeValue AS av ON bu.BusinessUnitID = av.ParentID INNER JOIN
tbAttributeValue AS av1 ON bu.BusinessUnitID = av1.ParentID INNER JOIN
tbLookup AS lu ON cs.PaymentTermID = lu.LookUpID
WHERE av.AttributeTemplateDefinitionLinkID = 30 
AND av1.AttributeTemplateDefinitionLinkID = 31
AND l.IsCurrent = 1
AND ul.LocationParentID = 20
AND cs.RangeEndByDate > '2016/09/01'  

Answer 1

Did you run the working query in the same query window, connected to the same server and database? What does the output of sp_help show?

EXEC sp_help N'tbLookup';

I can't say this will resolve you problem but it is a best practice to schema-qualify object names to avoid ambiguity and improve performance (e.g. dbo.tbLookup).

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 2

Thanks running sp_help did help, the problem was that tbLookup should have been tbLookUp.  I was running the script in Report Builder, having written it using SQL Server Management Studio.  Management Studio wasn't fussed about the spelling but Report Builder obviously was.  I was looking for a more complicated reason and it was something really simple.

Thanks for your help.