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 |
|
vijmeena
Starting Member
10 Posts |
 Posted - 2003-01-03 : 00:49:27
|
| I have 3 tables with the following data.Table 1Employee1Employee2Employee3Employee4Table 2Employee2 detailsEmployee3 detailsTable 3Employee1 detailsEmployee4 detailsHow can I write a single query without using a union to get the details of all employees.For e.gEmployee1 Employee1 detailsEmployee2 Employee2 detailsEmployee3 Employee3 detailsEmployee4 Employee4 details |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-01-03 : 03:46:47
|
| Erm. Well it depends how your tables are linked - I have to assume that there is some kind of relationship, like an employee ID... If your tables are exactly as you have shown in your example then I cannot do it, because there is no relationship. Are the columns actually called "Employee2 details" or is that just an example of the data contained in the tables? Can you post the DDL for your actual tables? If they are exactly as you have described you will, I guess, have to use some kind of dynamic SQL.-------Moo. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-03 : 06:56:49
|
| What's wrong with a UNION? I mean, if you are not going to normalize your scheme, why worry about performance either?Jay White{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-01-03 : 09:02:38
|
| I assume you want all the data on one row for a unique Employee_Id...if soSelect * From (Select * From Table1 Where Empl_Id = @EmplId) as a, (Select * From Table2 Where Empl_Id = @EmplId) as b, (Select * From Table3 Where Empl_Id = @EmplId) as cWhere a.Empl_Id = b.Empl_Id And a.Empl_Id = c.Empl_IdOf course I would reccomend not using *, but using the actual column names. You could also change it to be outer joins if Table1 is the driver and the other tables may or may not have data (that would be important). The predicate in the outer query is in case you want to eliminate the ones in the inner temp tables. It also depends on the relationships between the tables. If you have 1 to many relationships you will cartesian out...which might be ok for you...don't know with out more details.Good LuckBrett |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-03 : 09:28:37
|
| Assuming you have emp_id's in all these tables:SELECT T1.*, T2.*, T3.*FROMTable1 T1LEFT OUTER JOIN Table2 T2ON T1.Emp_ID = T2.EmpIDLEFT OUTER JOIN Table3 T3ON T1.Emp_ID = T3.EmpIDreplace the * with the fields you need from each table.is that what you mean?- Jeff |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-01-03 : 09:40:13
|
| I think it probably is, but it seems to me that you'd have to use at least one CASE statement somewhere along the line to produce output as shown in the question. Some of the employees are in table 2 but some are in table 3. Assuming there are emp_ids to join on and the tables are not literally as presented, then I'd suggestSELECT t1.employee, CASE when t2.employeedetail is not null then t2.employeedetailelse t3.employeedetail end as EmployeeDetailfrom table1 t1left outer join table2 t2 on t1.emp_id = t2.emp_idleft outer join table3 t3 on t1.emp_id = t3.emp_idWhich should produce roughly what was in the question, assuming that an employee's details EITHER appear in table 2 or table 3 but not both, and that there is an emp_id to join on, and that the columns are not actually named as in the example. There's too many assumptions for my liking, 's why I asked for proper DDL.-------Moo.Edited because I can't count.Edited by - mr_mist on 01/03/2003 09:41:24 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-03 : 10:01:30
|
| You could also use ISNULL() for each field:ISNULL(T2.Field1, T3.Field1) as Field1,ISNULL(T2.Field2, T3.Field2) as Field2,..etc..- Jeff |
|
|
|
|
|
|
|
|
SQL query required