SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Fetch all child records...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sroughley
Starting Member

4 Posts

Posted - 08/03/2005 :  10:52:55  Show Profile  Reply with Quote
Hi all.

Given the following basic data structure:

I need a query that will return all related records (the company record and all financial and person records) if Person.UpdateDate or Company.UpdateDate or Financial.UpdateDate is greater than a given date.

Any help here is greatly appreciated.

Stephen.

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/03/2005 :  11:00:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
you need to join these tables by the key column

Something like

Select C.col1,C.col2,P.col1,P.col2,F.col1,F.col2
from Company C inner join Person P on C.keycol=P.keycol
inner join Finance F on C.keycol=F.keycol

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sroughley
Starting Member

4 Posts

Posted - 08/03/2005 :  11:25:12  Show Profile  Reply with Quote
OK. The thing is I had already constructed a query along the lines of:

SELECT * FROM Company
LEFT OUTER JOIN Company ON Person.CompanyID = Company.CompanyID
LEFT OUTER JOIN Company ON Financial.CompanyID = Company.CompanyID
WHERE Company.UpdateDate = [DateValue] OR Person.UpdateDate = [DateValue] OR Financial.UpdateDate = [DateValue]

The thing is that this will only return all of the child rows (Financial, or Person) if either the company has a matching date or all of the child records have a matching date. Otherwise it will only return matching child rows and I need all child rows to be returned.

Edited by - sroughley on 08/03/2005 11:25:59
Go to Top of Page

sroughley
Starting Member

4 Posts

Posted - 08/04/2005 :  04:18:02  Show Profile  Reply with Quote
Anybody? This is quite urgent.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/04/2005 :  05:04:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you post some sample data for each table and your expected result?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sroughley
Starting Member

4 Posts

Posted - 08/04/2005 :  07:16:10  Show Profile  Reply with Quote
Right. We seem to have sorted it. Its not perfect, but it does get all of the required information. We now have:

SELECT Person.PersonID FROM Person WHERE Person.CompanyID IN (
SELECT CompanyID FROM (
SELECT Company.CompanyID, Company.Name
FROM Company
WHERE (Company.UpdateDate > '2005-07-11 00:00:00')
GROUP BY Company.CompanyID, Company.Name
UNION
SELECT Company.CompanyID, Company.Name
FROM Person
RIGHT OUTER JOIN Company ON Person.CompanyID = Company.CompanyID
WHERE (Person.UpdateDate > '2005-07-11 00:00:00')
GROUP BY Company.CompanyID, Company.Name
UNION
SELECT Company.CompanyID, Company.Name
FROM Financial
RIGHT OUTER JOIN Company ON Financial.CompanyID = Company.CompanyID
WHERE (Financial.UpdateDate > '2005-07-11 00:00:00')
GROUP BY Company.CompanyID, Company.Name
) As IndexTable
)

Which gets all of the person records, and we can then run a similar query to get all of the financial records, etc. As I say, its not perfect, as I would like to have achieved this via a single query if possible.

Posting the desired data to try to explain what we are trying to do would be very fiddly, but hopefully this will give you an idea of exactly what we are trying to achieve, so if you have any ideas for a more elegant solution, I'd still be very keen to hear them.

Thank you for your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000