Fetch all child records... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fetch all child records...

Author

Topic

sroughley
Starting Member

4 Posts

Posted - 2005-08-03 : 10:52:55

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

22864 Posts

Posted - 2005-08-03 : 11:00:41

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

sroughley
Starting Member

4 Posts

Posted - 2005-08-03 : 11:25:12

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.

sroughley
Starting Member

4 Posts

Posted - 2005-08-04 : 04:18:02

Anybody? This is quite urgent.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 05:04:42

Can you post some sample data for each table and your expected result?

Madhivanan

Failing to plan is Planning to fail

sroughley
Starting Member

4 Posts

Posted - 2005-08-04 : 07:16:10

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources