Inner join with sub query in PostgreSQL

Question

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.

Fahmi · Accepted Answer · 2018-09-10 09:50:30Z

1

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

answered Sep 10, 2018 at 9:50

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad Omer Aslam · Accepted Answer · 2018-09-10 12:50:22Z

0

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

edited Sep 10, 2018 at 12:50

Muhammad Omer Aslam

23.9k9 gold badges47 silver badges69 bronze badges

answered Sep 10, 2018 at 10:21

Danish Ali

298 bronze badges

2 Comments

404 Over a year ago

Edit your original answer instead of posting another. And format your code properly.

user330315 Over a year ago

Also please read this to learn how to properly format code

Collectives™ on Stack Overflow

Inner join with sub query in PostgreSQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related