CODEWITHSUNDEEP
sqlpostgresql
StrugglingCoder
StrugglingCoder

Reputation: 5021

Inner join with sub query in PostgreSQL

In my query I need to join with a sub query on a derived column :

select w1.wk_id,
(floor(td1.military_hour/4)*4) as military_hour_group ,
w1.end_date as end_date
from work_instances w1
inner join time_table td1 on w1.end_time = td1.time_id
inner join 
(
  select (floor(td2.military_hour/4)*4) as td2_military_hour_group, 
 (floor(td3.military_hour/4)*4) as td3_military_hour_group, wk_id
  from task_instances t1
  inner join time_table td2 on t1.end_time = td1.time_id
  inner join time_table td3 on t1.start_time = td3.time_id  
) tq1
on tq1.td2_military_hour_group = military_hour_group
and tq1.td3_military_hour_group = military_hour_group
and tq1.wk_id = w1.wf_id

It says Invalid operation: column "military_hour_group" does not exist in w1, td1, unnamed_join, tq1;

What am I doing wrong? Please help.

Upvotes: 0

Views: 96

Answers (2)

Danish Ali
Danish Ali

Reputation: 31

Okay Use like below mention

select *
from (select w1.wk_id, (floor(td2.military_hour/4)*4) as military_hour_group , w1.end_date as end_date
    from work_instances w1 inner join time_table td1 on w1.end_time = td1.time_id) table1 inner join (select (floor(td2.military_hour/4)*4) as td2_military_hour_group, (floor(td3.military_hour/4)*4) as td3_military_hour_group, wk_id
    from task_instances t1 inner join time_table td2 on t1.end_time = td1.time_id inner join time_table td3 on t1.start_time = td3.time_id ) tabl2 on tabl2.td2_military_hour_group = table1.military_hour_group and tabl2.td3_military_hour_group = table1.military_hour_group and tabl2.wk_id = table1.wf_id

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try below: military_hour_group is your calculated column and that's why it is showing that error

select w1.wk_id,
(floor(td1.military_hour/4)*4) as military_hour_group ,
w1.end_date as end_date
from work_instances w1
inner join time_table td1 on w1.end_time = td1.time_id
inner join 
(
  select (floor(td2.military_hour/4)*4) as td2_military_hour_group, 
 (floor(td3.military_hour/4)*4) as td3_military_hour_group, wk_id
  from task_instances t1
  inner join time_table td2 on t1.end_time = td1.time_id
  inner join time_table td3 on t1.start_time = td3.time_id  
) tq1
on tq1.td2_military_hour_group = (floor(td1.military_hour/4)*4)
and tq1.td3_military_hour_group = (floor(td1.military_hour/4)*4)
and tq1.wk_id = w1.wf_id

Upvotes: 1

Related Questions