How to write join using three tables

Posted by Johnseelan under Sql Server on 10/18/2012 | Points: 10 | Views : 842 | Status : [Member] | Replies : 3
Hi All,
I have a table Employee
______________________
|EmpId | name | Desig |
|---------|---------|---------|
| | | |
--------------------------------
Then OnsiteEmp
______________________
|EmpId| LocationId|Details|
|--------|----------------|---------|
| | | |
|--------|---------------|----------|

Then Location
______________________
|LocationId| LocationName|Details|
|-------------- |--------------------|---------|
| | | |
|---------------|--------------------|---------|

i need Out put like only the employee who are all working in Offshore(india),
i was try like this but it will give dublicate recoud

SELECT a.EmpId,a.EmpName FROM dbo.Employee AS a
JOIN dbo.OnsiteEmp AS b ON a.EmpId != b.OnSiteEmpId
JOIN dbo.LocationTable AS c ON c.LocationId!=b.LocationId.

please help me to get correct record




Responses

Posted by: Self-Innovator on: 10/18/2012 [Member] Bronze | Points: 25

Up
0
Down
Try with this
select e.name,o.Details,l.LocationName from employee e 

inner join OnsiteEmp o on e.EmpId=o.EmpId
inner join Location l on o.LocationId=l.LocationId where l.LocationName='Offshore(India)'


Join Hands Change lives
Thanks & Regards
Straight Edge Society

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

Posted by: Sourabh07 on: 10/18/2012 [Member] Starter | Points: 25

Up
0
Down
Try this query, hopefully it will solve your problem.....

Select distinct A.EmpId, A.Name, A.Desig, B.Details as OnSiteDetails, 

C.LocationName, C.Details as LocationDetails
From Employee A
Inner Join OnsiteEmp B on A.EmpId = B.EmpId
Inner Join Location C on C.LocationId=lBLocationId
where C.LocationName = 'Offshore(India)'


Sourabh07

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

Posted by: Naraayanan on: 11/1/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
Try this
Create view for this Query
Create View viewOffshore

as
Select distinct a.empId,a.Details from Onsite ,Location b,Location a where a.LocationId != b.LocationId

and
Create a Sp for this query:
Create Procedure Offshore

as
Select distinct v.empid,v.Details,a.name from Offshore v,Emp a where a.empId=v.empId


Regards,
Lakshmi Naraayanan.S
http://dotnettechrocks.blogspot.in/
http://abaprocker.blogspot.com/

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

Login to post response