Select all children of parent where >1 child.DateCompleted not null

Hi,

I have an existing select query in reporting services that returns Jobs that have Joblines. The Jobline may have a DateCompleted set. What i'd like to do is return all Joblines within the Job if one or more DateCompleted's are set, if that makes sense ?

Sorry for the description, it's all part of being a novice ;-)  

thanks
LVL 1
Steve JebsonDeveloperAsked:
Who is Participating?
 
dbbishopConnect With a Mentor Commented:
Assuming you have a JOBID in Jobs and a JOBID foreign key in JobLines then
SELECT j.JobID, j.Other Job Fields, jl.DateCompleted
FROM Jobs j
INNER JOIN JobLines jl ON j.JobID = jl.JobID
WHERE j.JobID IN (SELECT JobID FROM JobLines WHERE DateCompleted IS NOT NULL)
0
 
Steve JebsonDeveloperAuthor Commented:
Perfect !

Seems obvious now you've spelt it out for me ;-)
0
 
dbbishopCommented:
Thanks.
I know what you mean. I've asked 50-times more questions than I've answered.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.