February 17, 2012 at 8:14 am
Hi all,
I created a query to return data from 4 fields in 1 table and 1 field in another table.
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).
I believe JobSummary table holds the key to getting the return correct because it ties jobnum and CustId fields together.
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 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.
I cannot figure for the life of me the reason this logic returns duplicates. Any ideas?
February 17, 2012 at 9:23 am
Hi and welcome to SSC! It will help you get better answers if you can post ddl (create table scripts), sample data (insert statements) and desired results based on that sample data. That way we have something work with and often you will find that in gathering this information you solve the issue before you even post your question.
Take a look at the first link in my signature for best practices on posting questions to give you a better chance at an answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2012 at 10:33 am
Your sample query has columns not listed in your table columns, i.e. Step, CurrentStep.
It seems the natural way to join the tables would be
select jd.Jobnum, jd.TimeStart, jd.Stepnum, jd.tgtQName, a.RunInSingleMode
from JobDetail jd
inner join JobSummary js on jd.JobNum = js.JobNum and jd.StepNum = js.StepNum
inner join Application a on js.CustID = a.CustID and js.StepNum = a.StepNum
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply