Click here to Skip to main content
14,641,302 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello!!

I have a employee table in which there are entries for present District address and for permanent district address. I also have a table master_district in which there are some 900 districts. now there are two entries of district for each employee in master_employee. my problem is that how to use inner join to get district whose id is stored in master_employee.
there are two entries for district so if i am using

Select me.name,me.name_H,me.nationality,me.cast,me.sex,me.permanent_address,ms.state_name,md.dist_name,me.present_address,ms.state_name,md.dist_name,me.father_husband_name,me.date_of_birth,me.personal_id_mark,me.Qualifications from master_employee me  inner join master_state ms on me.state_present_address=ms.state_id   inner join master_district md on me.dist_permanent_address=md.dist_id where me.unqid=54

then because join is created only on 1 condition for district so it is showing same district address for both id's how ever if i use
Select me.name,me.name_H,me.nationality,me.cast,me.sex,me.permanent_address,ms.state_name,md.dist_name,me.present_address,ms.state_name,md.dist_name,me.father_husband_name,me.date_of_birth,me.personal_id_mark,me.Qualifications from master_employee me  inner join master_state ms on me.state_present_address=ms.state_id  and me.state_permanent_address =ms.state_id inner join master_district md on me.dist_permanent_address=md.dist_id and me.dist_present_address=md.dist_id where me.unqid=12


then there is no data shown for the command. Please tell me how to use inner join to get name for both the district according to there district id
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 2

Select me.name,
me.name_H,
me.nationality,
me.cast,
me.sex,
me.permanent_address,
(Select state_name from  master_state where state_id=me.state_permanent_address)
as PermanentState, 
(Select dist_name from  master_district where dist_id=me.dist_permanent_address) as PermanentDistrict,
me.present_address,
(Select state_name from  master_state where state_id=me.state_present_address)
as PresentState,
(Select dist_name from  master_district where dist_id=me.dist_present_address) as PresentDistrict,
me.father_husband_name,
me.date_of_birth,
me.personal_id_mark,
me.Qualifications 
from master_employee me  

where me.unqid=54
   
v2
Comments
ujjwal uniyal 25-Jul-12 3:42am
   
Thanks santhosh. it works . :)
Santhosh Kumar Jayaraman 25-Jul-12 4:47am
   
Welcome
Rate this:
Please Sign up or sign in to vote.

Solution 1

use 'left join' instead of 'inner join'

Happy Coding!
:)
   
Comments
ujjwal uniyal 25-Jul-12 3:34am
   
using left join it's showing me data but the problem still remains the same as it's showing same district name for different district id values
Aarti Meswania 25-Jul-12 3:37am
   
Select me.name,me.name_H,me.nationality,me.cast,me.sex,me.permanent_address,
ms.state_name,md.dist_name,me.present_address,ms.state_name,md.dist_name,me.father_husband_name,
me.date_of_birth,me.personal_id_mark,me.Qualifications
from master_employee me
inner join master_state ms on me.state_present_address=ms.state_id
Left join master_district md on me.dist_permanent_address=md.dist_id
where me.unqid=54

check this
ujjwal uniyal 25-Jul-12 3:41am
   
same result as was with the last query. BDW Thanks for the help.. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100