Sql subquery for DB2 -
the sql query requires 4 tables joined, did, , have display few columns out of them satisfy condition. query in clause. how write subquery.. display column (org_name,that there in org_unit) contents based on rows satified query in clause. wrote code, not working me:
select t33.contract_num, t135.minor_org_num, t96.org_type,t22.cfd_flag, (select t96.org_name org_unit t96, sub_unit t135 t96.org_number in (t135.major_org_number)) head_org_name org_unit t96, sub_unit t135, cust_contract t33, cont_assignmt t22 t96.org_number = t22.org_number , t22.ctr_sys_num = t33.ctr_sys_num , t96.org_number = t135.minor_org_number , t135.reltn_type = 'hos' , t22.cfd_flag = 'y';
for record, t135 contains head offices numbers (major_org_number) , sub - offices numbers (minor_org_number)
in sql, use join "merge" tables based on common columns.
here simple guide give base idea: sql join
in sql, it's best draw want do, refer link see "left join" picture example: left join
using "left join" merge tables (where : org_unit.org_number = sub_unit.major_org_number), this:
left join sub_unit t135 on t96.org_number = t135.major_org_number
in query, put join right after "from", , before "where":
select t33.contract_num, t135.minor_org_num, t96.org_type, t22.cfd_flag, t135.org_name head_org_name org_unit t96, cust_contract t33, cont_assignmt t22 left join sub_unit t135 on t96.org_number = t135.major_org_number t96.org_number = t22.org_number , t22.ctr_sys_num = t33.ctr_sys_num , t96.org_number = t135.minor_org_number , t135.reltn_type = 'hos' , t22.cfd_flag = 'y';
notice, (and should) use join merging tables (and avoiding using expensive condition):
select t33.contract_num, t135.minor_org_num, t96.org_type, t22.cfd_flag, t135.org_name head_org_name org_unit t96 left join sub_unit t135 on t96.org_number = t135.major_org_number , t96.org_number = t135.minor_org_number left join on cont_assignmt t22 on t96.org_number = t22.org_number left join on cust_contract t33 on t22.ctr_sys_num = t33.ctr_sys_num t135.reltn_type = 'hos' , t22.cfd_flag = 'y';
there several join types (left/right/inner/outer), see using 1 need.
Comments
Post a Comment