[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Query not pulling information from joined table

I have the following code in a stored procedure that sends an email with the information.  Everything works fine except that it's not getting any data from the tbl_ProjectTrackingContracts (as ct) table.  What am I missing?
0
Kevin Smith
Asked:
Kevin Smith
  • 2
1 Solution
 
Dale BurrellCommented:
Did you forget to attach the SQL?
0
 
Kevin SmithAuthor Commented:
Oops..Ha...

SELECT
                td = p.JobNumber    , ''
                , td = ISNULL(pt.ProjectTitle,'')   ,  ''
                , td = ISNULL(l.PropertyTitle,'')   ,  ''
               , td = ISNULL(pt.MProjectNumber,'')   ,  ''
                , td = ISNULL(pm.PMName,'')   ,  ''
                , td = ISNULL(pm.PMCoordinator,'')   ,  ''
               
                , td = ISNULL(replace(convert(char(10), ct.ContractedExecuted, 101), '-', ' '),CONVERT(char(8),''))     ,  ''
                , td = ISNULL(ct.ContractExecutedSt,'')   ,  ''
                  , td = ISNULL(replace(convert(char(10), ct.COI, 101), '-', ' '),CONVERT(char(8),''))     ,  ''
                , td = ISNULL(ct.COISt,'')   ,  ''
                , td = replace(convert(char(10), pt.ConstStart, 101), '-', ' ')    ,  ''

                                           
              FROM (projmgmtsrv.dbo.tbl_ProjectTracking AS pt
              LEFT JOIN projmgmtsrv.dbo.tbl_Jobs AS p
              ON pt.ProjectID = p.ProjectID)
              LEFT JOIN projmgmtsrv.dbo.tbl_PM AS pm ON p.PM = pm.PMID                
              LEFT JOIN projmgmtsrv.dbo.tbl_Locations AS l ON p.Location = l.LocationID
              LEFT JOIN projmgmtsrv.dbo.tbl_ProjectTrackingContracts AS ct ON pt.ProjectID = ct.ProjectTrackingID
               where pt.ConstStart between getdate() and dateadd(dd,14,getdate()) and (ct.ContractExecutedSt = 'NO' OR ct.ContractExecutedSt = 'N/A' OR ct.ContractExecutedSt is null or ct.COIst = 'NO' or ct.COIst = 'N/A' or ct.COIst is null)
              order by pt.ConstStart asc
0
 
Dale BurrellCommented:
This won't be the cause, but is there any reason you giving every column the same alias ('td').

The join is syntactically OK, the question is whether the conditions are correct.

Without having access to the data there probably isn't much anyone can do to help.

Have you run the query manually and played with the relevant conditions to see what is happening?
0
 
Paul_Harris_FusionCommented:
As Dale says,  tough without the data and your expected results.
That said, the where clause looks odd to me since you have some constraints on a table retrieved via a left join.

Your current SQL:
              LEFT JOIN projmgmtsrv.dbo.tbl_ProjectTrackingContracts AS ct ON pt.ProjectID = ct.ProjectTrackingID
               where pt.ConstStart between getdate() and dateadd(dd,14,getdate()) and (ct.ContractExecutedSt = 'NO' OR ct.ContractExecutedSt = 'N/A' OR ct.ContractExecutedSt is null or ct.COIst = 'NO' or ct.COIst = 'N/A' or ct.COIst is null)

Could that be a source of confusion?  
Should some of the conditions be in the ON clause?   This might produce different results since you are using an outer join.
e.g.

LEFT JOIN projmgmtsrv.dbo.tbl_ProjectTrackingContracts AS ct
ON pt.ProjectID = ct.ProjectTrackingID
  and (ct.ContractExecutedSt = 'NO' OR ct.ContractExecutedSt = 'N/A' OR  
  ct.ContractExecutedSt is null or ct.COIst = 'NO' or ct.COIst = 'N/A' or ct.COIst is null)
WHERE
  pt.ConstStart between getdate() and dateadd(dd,14,getdate())
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now