SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 One Master and Two Detail Tables Join
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mfdarvesh
Starting Member

10 Posts

Posted - 12/12/2005 :  06:38:01  Show Profile
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
22769 Posts

Posted - 12/12/2005 :  06:43:18  Show Profile  Send madhivanan a Yahoo! Message
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
Go to Top of Page

surendrakalekar
Posting Yak Master

India
120 Posts

Posted - 12/12/2005 :  06:46:52  Show Profile
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
Go to Top of Page

mfdarvesh
Starting Member

10 Posts

Posted - 12/12/2005 :  06:56:50  Show Profile
surendrakalekar, One to many from Emp -> edu and One to many from emp -> Exp

madhivanan, the query return error in join
Go to Top of Page

surendrakalekar
Posting Yak Master

India
120 Posts

Posted - 12/12/2005 :  07:46:56  Show Profile
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
Go to Top of Page

mfdarvesh
Starting Member

10 Posts

Posted - 12/12/2005 :  07:58:27  Show Profile
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
Go to Top of Page

surendrakalekar
Posting Yak Master

India
120 Posts

Posted - 12/13/2005 :  01:38:58  Show Profile
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
Go to Top of Page

mfdarvesh
Starting Member

10 Posts

Posted - 12/13/2005 :  02:02:11  Show Profile
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.
Go to Top of Page

surendrakalekar
Posting Yak Master

India
120 Posts

Posted - 12/13/2005 :  02:11:17  Show Profile
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/13/2005 :  02:15:24  Show Profile  Send madhivanan a Yahoo! Message
Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mfdarvesh
Starting Member

10 Posts

Posted - 12/13/2005 :  02:46:34  Show Profile
dear Madhivanan can you given email address, I can send you direct file because here it is not possible to attch file
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/13/2005 :  02:56:52  Show Profile  Send madhivanan a Yahoo! Message
No need to attach files. Paste the data here

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000