One Master and Two Detail Tables Join

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

Old Forums

CLOSED - General SQL Server

One Master and Two Detail Tables Join

Author

Topic

mfdarvesh
Starting Member

10 Posts

Posted - 2005-12-12 : 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

22864 Posts

Posted - 2005-12-12 : 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

120 Posts

Posted - 2005-12-12 : 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 - 2005-12-12 : 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

120 Posts

Posted - 2005-12-12 : 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 - 2005-12-12 : 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

120 Posts

Posted - 2005-12-13 : 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

mfdarvesh
Starting Member

10 Posts

Posted - 2005-12-13 : 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

120 Posts

Posted - 2005-12-13 : 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

22864 Posts

Posted - 2005-12-13 : 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 - 2005-12-13 : 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

22864 Posts

Posted - 2005-12-13 : 02:56:52

No need to attach files. Paste the data here

Madhivanan

Failing to plan is Planning to fail