Query in oracle11g

Question

i have 2 tables, voters & candidates. i want to display the candidate_name from candidate table only using the voter_id in voter table. only those candidate name who are the candidate in that particular voter's area like panchayat_number,ward_number.

table creation query:

create table voters(voter_id varchar2(30) not null,

voter_name varchar2(30) not null,
voter_father_name varchar2(30),
voter_address varchar2(100) not null,
sex varchar2(10) not null,
DOB date not null, 
panchayat_number varchar2(30), 
ward_number varchar2(30),
Assembly_number varchar2(30) not null, 
Parlament_number varchar2(30) not null,
constraint vtr_vtr_id_pk primary key(voter_id))

create table candidates(candidate_id varchar2(20) not null, 
candidate_name varchar2(30) not null,
candidate_age number(10) not null,
candidate_address varchar2(100) not null,
area_code varchar2(20) not null,
candidate_voter_id varchar2(20) not null, 
sex varchar2(6) not null ,
political_party varchar2(20) not null,
total_votes number(30) not null, 
constraint cnd_cnd_id_pk primary key(candidate_id),
constraint cnd_cnd_vid_fk foreign key(candidate_voter_id) references voters(voter_id))

Voters Table:

Candidates Table:

so now voters want to see the candidates name in his area using voter's voters_id.

Answer 1

Your query looks like:

select 
  candidate_name
from 
  candidates 
join
  voters 
on
  voter_id=candidate_voter_id
where
(panchayat_number,ward_number)
in 
(Select panchayat_number,ward_number from Voters where Voter_id= <input voter id>) ;