quote:

---

Originally posted by dreinhard

Hi Visakh16,

I have tried numerous testing with joins, inner joins and multiple joins to no success. My closest results were dublicate returns. I will try to be exact with what I am trying to do:

I have 3 tables. Here are the feilds in each table that I feel are important:

JobDetail

JobNum
TimeStart
TimeEmd
tgtQName
StepNum

JobSummary

JobNum
StepNum
CustID

Application

CustID
StepNum
RunInSingleMode

I need to return 4 values from JobDetail and 1 value from Application

For JobDetail I need to return JobNum, StepNum, TimeStart and tgtQName.
For Application I need to return RunInSingleMode (True or False).

Here is my SQL Statement:

SELECT JobDetail.Jobnum, JobDetail.TimeStart, JobDetail.Stepnum, JobDetail.tgtQName, [Application].RunInSingleMode FROM JobDetail JOIN JobSummary ON JobDetail.JobNum = JobSummary.JobNum JOIN [Application] ON JobSummary.CustID = [Application].CustID JOIN JobDetail as JD2 ON RIGHT([Application].Step, 1) = JOBSUMMARY.CurrentStep WHERE JobDetail.TimeEnd is null AND JobDetail.TimeStart is not null AND JobSummary.TimeEnd is null AND JOBSUMMARY.TimeStart is not null ORDER BY JobDetail.TimeStart ASC

With this query I am having no problems getting data to return BUT I am receiving duplicates. Each one of these returns should be unique because in JobDetail you cannot have 2 of the same JobNums with the same StepNum with a null TimeEnd running at the same time. Part of the problem is that I am trying to get for each return, the value of Application.RunInSingleMode. I know the way to get those values is to relate JobDetail.Jobnum with JobSummary.Jobnum And JobDetail.StepNum with JobSummary.CurrentStep then find the value of JobSummary.CustId and JobSummary.CurrentStep and relate it to Application.CustId and Application.StepNum to get that value of Application.RunInSingleMode.

Run reason this logic returns duplicates. Any ideas?

---