Kevin Smith
asked on
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_ProjectTrackingContrac ts (as ct) table. What am I missing?
Did you forget to attach the SQL?
ASKER
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(cha r(10), ct.ContractedExecuted, 101), '-', ' '),CONVERT(char(8),'')) , ''
, td = ISNULL(ct.ContractExecuted St,'') , ''
, td = ISNULL(replace(convert(cha r(10), ct.COI, 101), '-', ' '),CONVERT(char(8),'')) , ''
, td = ISNULL(ct.COISt,'') , ''
, td = replace(convert(char(10), pt.ConstStart, 101), '-', ' ') , ''
FROM (projmgmtsrv.dbo.tbl_Proje ctTracking 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_Locati ons AS l ON p.Location = l.LocationID
LEFT JOIN projmgmtsrv.dbo.tbl_Projec tTrackingC ontracts 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
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(cha
, td = ISNULL(ct.ContractExecuted
, td = ISNULL(replace(convert(cha
, td = ISNULL(ct.COISt,'') , ''
, td = replace(convert(char(10), pt.ConstStart, 101), '-', ' ') , ''
FROM (projmgmtsrv.dbo.tbl_Proje
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_Locati
LEFT JOIN projmgmtsrv.dbo.tbl_Projec
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
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.