| Author |
 Topic  |
|
|
mfdarvesh
Starting Member
10 Posts |
 Posted - 12/12/2005 : 06:38:01
|
I have three tables Emp, Edu and Exp the relationship between them is like this:
Emp -> Edu (Empid)
Emp -> Exp (Empid)
both tables are linked with EmpId in a Sinlge master table. I want to retrive all the information from three tables and dispaly them on a single page. Is is possible, below is the query which i am using and it gives wrong resut.
Select e.empid, d.qualifcation, p.nature
from emp e, edu d, exp p
where e.empid = d.empid
and e.empid = p.empid
How can I correct that, the same result is obtained if i use inner join our left outer join, please help me |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/12/2005 : 06:43:18
|
Try this
Select e.empid, d.qualifcation, p.nature
from emp e inner join edu d on e.empid = d.empid
inner join exp p on e.empid = p.empid
Otherwise post some sample data and the result you want
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
surendrakalekar
Posting Yak Master
India
120 Posts |
Posted - 12/12/2005 : 06:46:52
|
Logically query is correct.
One doubt.
R u sure the relation between Emp->Edu and Emp->Exp is one to one? ... or is it one to many?
Surendra |
|
|
|
mfdarvesh
Starting Member
10 Posts |
Posted - 12/12/2005 : 06:56:50
|
surendrakalekar, One to many from Emp -> edu and One to many from emp -> Exp
madhivanan, the query return error in join |
|
|
|
surendrakalekar
Posting Yak Master
India
120 Posts |
Posted - 12/12/2005 : 07:46:56
|
quote:
Originally posted by mfdarvesh
surendrakalekar, One to many from Emp -> edu and One to many from emp -> Exp
madhivanan, the query return error in join
One to many...
It will be better if you post the structure of your tables with some sample data and let us know the desired output.
Surendra |
|
|
|
mfdarvesh
Starting Member
10 Posts |
Posted - 12/12/2005 : 07:58:27
|
Table Emp
empid (pk)
name
address etc.
table edu
empid (fk)
degree
year
grade
table exp
empid (fk)
period
duties etc.
these are three tables, edu and exp table may have diffrent number of rows for a single employee, My boss wants that all the information may be displayed on single sheet, this is actually problem |
|
|
|
surendrakalekar
Posting Yak Master
India
120 Posts |
Posted - 12/13/2005 : 01:38:58
|
As per your postings you required the result in the below given format. Correct me if I am wrong.
empid qualification nature
1 BE Officer
1 MBA Admin
1 NULL MD
2 MCS Manager
2 PHD NULL
3 B.COM Worker
3 M.COM Officer
3 MCA Asst. Manager
4 BCS Developer
4 NULL TL
4 NULL PL
To achieve such kind of result either you need to change your table structure. OR
You can achieve this by using the #temp table and cursors. By using cursors you can store the data into #temp table and then get back those result to display. Your #Temp table must have all the required fields from all the tables and put NULL where the record/values are not exists.
Surendra |
Edited by - surendrakalekar on 12/13/2005 01:39:32 |
|
|
|
mfdarvesh
Starting Member
10 Posts |
Posted - 12/13/2005 : 02:02:11
|
yes Surendra you are right, my boss want to print actually all this information on a single page because each employee has few lines in education as well as in experience. So all that information can be printed on single page but I was unable create a view for that. If I can use #table, please guide me how can do that..
If you are so kind to me, please tell me how I can change the structure of tables. I actually want to keep some more information too, like medical record, family etc. |
|
|
|
surendrakalekar
Posting Yak Master
India
120 Posts |
Posted - 12/13/2005 : 02:11:17
|
1. Create #temp table which is having columns like empid, qualification, nature
2. Create cursor for both EDU and EXP tables
3. Fetch field values into variables.
4. Loop both of them (one inside another)
5. Insert values into #temp table
6. goto next records.....
7. close/deallocate the cursor
8. display the result.
9. remove the #temp table.
Surendra |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/13/2005 : 02:15:24
|
Can you post some sample data and the result you want?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
mfdarvesh
Starting Member
10 Posts |
Posted - 12/13/2005 : 02:46:34
|
dear Madhivanan can you given email address, I can send you direct file because here it is not possible to attch file
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/13/2005 : 02:56:52
|
No need to attach files. Paste the data here
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
One Master and Two Detail Tables Join
Forum Locked
