Use above two tables and only JOINS

Posted by Cnagasa under Sql Server on 1/9/2012 | Points: 10 | Views : 819 | Status : [Member] | Replies : 3
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


Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response