locked
Inner join problem RRS feed

  • 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'  



    Friday, September 9, 2016 3:43 PM

Answers

  • 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

    Friday, September 9, 2016 3:50 PM

All replies

  • 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

    Friday, September 9, 2016 3:50 PM
  • 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.


    Monday, September 12, 2016 9:00 AM