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 |
|
CactusJuice
Starting Member
46 Posts |
 Posted - 2005-03-04 : 08:47:58
|
| Could I please ask for your input on this? I am querying for a list of documents. The query will return hundreds of Document records based on the matching EmployeeID. My difficulty is the company is using a split system (a legacy table named Tasks and new table named Projects. A Document record either have a matching start date in Projects or Tasks table...but not both.For example a query on EmployeeID 12345678 might have 50 records in table Documents. Of these, 20 might have their StartDate record in table Projects and 30 in table Tasks. Any ideas on how I might approach this?SELECT d.DocumentID, d.DocumentName, d.EmployeeID, DocumentStartDate FROM Documents d[INNER JOIN Projects or Tasks ON EmployeeID]WHERE d.EmployeeID = @EmployeeIDDocuments Projects Tasks---------- -------- -----DocumentID ProjectID TaskIDDocumentName StartDate StartDatethanks,Cameron |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-04 : 09:09:23
|
| It appears that both projects and Tasks have a many-to-one relationship with Employees? If so, you cannot join two different transaction tables in the same query. If you want to list all Projects and all Tasks for a given employee, and they are in different tables, you should use a UNION to put the results "on top of" each other. BEcause other than EmployeeID, there is no relation between the Projects and the Tasks (I assume unless you give more details) so they cannot exist in the same SQL unless they are both previously summarized by EmpID.- Jeff |
 |
|
|
CactusJuice
Starting Member
46 Posts |
Posted - 2005-03-04 : 09:17:24
|
| Thanks Jeff. A UNION will hurt because there are hundreds of thousands of records in both tables. Is there any way to do it with a CASE? Each Document has only 1 related record, either in Projects or Tasks table. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-04 : 09:25:23
|
| oh -- then you would do a LEFT OUTER JOIN to BOTH tables at the same time. Then you can use a CASE statement to return values from whichever join doesn't result in NULLS.That's really the best way to do a "conditional join" in SQL -- do each condition as a separate left outer join, and then use a CASE statement to evaluate the condition and determine which outer table to pull from.That's definitely the way to do it, give it a shot and let me know how it goes. If you have troubles, then you'll need to post the structure of your 3 tables (just important columns, not all of them) and some sample data from each (just enough rows to re-create all possibile conditions and situations).- Jeff |
|
|
|
|
|
|
|
|
Conditional JOIN logic, is it possible?