Query Help required Urgent -- sql server programming

Jami
2/17/2007 9:53:07 PM

Hi

i have following two tables and sample data

create Table Main_Tab (E_no int,EName varchar(30),FName
Varchar(30),Ph_no varchar(10))
go
insert into Main_Tab values (1,'Salim','Malik','121112121')
insert into Main_Tab values (2,'Zahid','Rauf','32223233')
insert into Main_Tab values (3,'Imran','Nazir','980880001')
insert into Main_Tab values (4,'Salim','Malik','121112121')
insert into Main_Tab values (5,'Farooq','Zafar','242426672')
insert into Main_Tab values (6,'Imran','Nazir','980880001')
insert into Main_Tab values (7,'Nadeem','salman','32323222')
insert into Main_Tab values (8,'wasim','Ali','232324234')
insert into Main_Tab values (9,'sadia','Kashif','2232323')
insert into Main_Tab values (10,'Farooq','Zafar','242426672')
go

create table double_Tab(E_no1 int,Ref_no1 smallint, E_no2 int, Ref_no2
smallint)

insert into double_Tab values(1,100,4,200)
insert into double_Tab values(3,101,6,201)
insert into double_Tab values(5,102,10,202)

Main_tab has employees personal data and double_tab has data about those
employees which get registered twice like in the abve example E_no '1'
registered again with E_no '4'

i want output like

E_no1 Ename Fname Ref_No1 E_no2 Ename Fname Ref_no2

1 Salim Malik 100 4 Salim Malik 200
3 Imran Nazir 101 6 Imran Nazir 201
..
..
..

i m not fiquing out the query for this scenario

Regards
Jami

Roji. P. Thomas
2/18/2007 12:00:00 AM

SELECT A.E_no, A.EName, A.FName, A.Ph_no, C.E_no, C.EName, C.FName, C.Ph_no

FROM Main_Tab A INNER JOIN double_Tab B ON a.E_No = B.E_No1

INNER JOIN Main_Tab C ON C.E_no = B.E_no2

[quoted text, click to view]

sql server programming : Query Help required Urgent