Use above two tables and only JOINS

Posted by Cnagasa under Sql Server on 1/9/2012 | Points: 10 | Views : 1795 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi everyone,

I have 2 tables one table Will be Employee another table will be employee address
Employee
ID Name
1 Sudarshan

EmployeeAddress
ID AddressType Address
1 100 XYZ (100 = Permanent)
1 200 ABC (200 = Temporary)

Q. Use above two tables and only JOINS and display the result like this
Result
ID Name Permanent Address Temporary Address
1 Sudarshan XYZ ABC

plz send me the join query we have not to use any thing pivot method

Regards
Sudarshan.C

sudarshan.c

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Blessyjees on: 1/9/2012 [Member] Bronze | Points: 25

0	Hi, Please try this query select e.id,e.name,address1.address as 'Permanent Address',address2.address as 'Temporary Address' from employee e left join (select * from address where type='100') as address1 on e.id=address1.ID left join (select * from address where type='200') as address2 on e.id=address2.ID Blessy Baby Digitalmesh Softech pvt Ltd https://blessybaby.wordpress.com/ Cnagasa, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

Please try this query

  select e.id,e.name,address1.address as 'Permanent Address',address2.address as 'Temporary Address' from employee e

  left join (select * from address where type='100') as address1 

  on e.id=address1.ID

  left join (select * from address where type='200') as address2 

  on e.id=address2.ID

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 1/9/2012 [Member] Silver | Points: 25

0	create table Employee_(id int,name varchar(20)) insert into Employee_(id,name) values(1,'Sudarshan') create table EmployeeAddress_(id int,empid int,addresstype int,Address varchar(100)) insert into EmployeeAddress_(id,empid,addresstype,Address) values (1,1,100,'XYZ') insert into EmployeeAddress_(id,empid,addresstype,Address) values (2,1,200,'ABC') select top 1 a.id,a.name,b.addresstype,b.Address , Address2=(select Address from EmployeeAddress_ where addresstype=200 and a.id=b.empid ) from Employee_ a left outer join EmployeeAddress_ b on a.id=b.empid Try This.. Thanks, Sanjay Cnagasa, if this helps please login to Mark As Answer. \| Alert Moderator

create table Employee_(id int,name varchar(20))

insert into Employee_(id,name) values(1,'Sudarshan')

create table EmployeeAddress_(id int,empid int,addresstype int,Address varchar(100))



insert into EmployeeAddress_(id,empid,addresstype,Address)

values (1,1,100,'XYZ')

insert into EmployeeAddress_(id,empid,addresstype,Address)

values (2,1,200,'ABC')





select top 1   a.id,a.name,b.addresstype,b.Address , Address2=(select Address from EmployeeAddress_  where addresstype=200 and a.id=b.empid )



from Employee_ a left outer join EmployeeAddress_ b

on a.id=b.empid

Try This..

Thanks,
Sanjay

Cnagasa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25

0	Employee ID Name 1 Sudarshan EmployeeAddress ID AddressType Address 1 100 XYZ (100 = Permanent) 1 200 ABC (200 = Temporary) select id,name from Employee inner join EmployeeAddress on Employee.id=EmployeeAddress.id where addresstype=100 Hope this will help Regards sriram Cnagasa, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts