problem with inner join

Question

4.00/5 (1 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

SQL

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 24-Jul-12 21:22pm

ujjwal uniyal

Add a Solution

2 solutions

Solution 1

use 'left join' instead of 'inner join'

Happy Coding!
:)

Posted 24-Jul-12 21:27pm

Aarti Meswania

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.. :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santhosh Kumar Jayaraman · Accepted Answer · 2012-07-24T21:38:00

SQL

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