Solved

Query not pulling information from joined table

Posted on 2013-11-04
4
156 Views
Last Modified: 2014-01-30
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
Comment
Question by:Kevin Smith
  • 2
4 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39623164
Did you forget to attach the SQL?
0
 

Author Comment

by:Kevin Smith
ID: 39623170
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39623195
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
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 500 total points
ID: 39623838
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to convert JSON file to csv? 7 52
Managing SQL log files, SQL Server 2014 6 55
Complex SQL 10 33
Group by and order by clause 28 36
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now