在oracle11g中查询

Question

我有2个表，选民和候选人。我只想在投票表中使用voter_id来显示来自候选表的candidate_name。只有那些候选人的名字是候选人的特定选民的领域，如panchayat_number，ward_number。

建表查询：

创建表投票人(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))

投票者表：

​

​

候选表：

​

​

因此，现在选民希望使用voter's voters_id查看他所在地区的候选人姓名。

Answer 1

您的查询如下所示：

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>) ;