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.
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 columnSomething like Select C.col1,C.col2,P.col1,P.col2,F.col1,F.col2from Company C inner join Person P on C.keycol=P.keycolinner join Finance F on C.keycol=F.keycolMadhivananFailing 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 CompanyLEFT OUTER JOIN Company ON Person.CompanyID = Company.CompanyIDLEFT 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?MadhivananFailing 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. |
|
|
|
|
|
|
|