[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-07-20
3
Medium Priority
?
189 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:stevejebson
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
dbbishop earned 2000 total points
ID: 19534414
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
 
LVL 1

Author Comment

by:stevejebson
ID: 19534613
Perfect !

Seems obvious now you've spelt it out for me ;-)
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19534712
Thanks.
I know what you mean. I've asked 50-times more questions than I've answered.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 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