CODEWITHSUNDEEP
sqlpostgresql
StrugglingCoder
StrugglingCoder

Reputation: 5021

How to avoid joining to a same dimension table twice

I have table task_instances that has task_id, end_time , start_time.

end_time and start_time links to time_table which contains military_hour format (24 hour ranging from 0 -23).

I want to know all the tasks where each tasks start time and end_time belong to the same military hour group ( there are 6 groups , each containing 4 hours).

  select (floor(td1.military_hour/4)*4) as td1_military_hour_group, 
  (floor(td2.military_hour/4)*4) as td2_military_hour_group, wk_id 
  from task_instances t1
  inner join time_table td1 on t1.end_time = td1.time_id
  inner join time_table td2 on t1.start_time = td2.time_id 
  where  td1_military_hour_group = td2_military_hour_group

Is it required to join same time_table twice. Can we better the query or even the query seems correct?

Upvotes: 0

Views: 169

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

You can't use the derived values td1_military_hour_group and td2_military_hour_group in the WHERE clause or you'll get an error, e.g. "Unknown column 'td1_military_hour_group' in 'where clause'". You'll need to repeat the calculations in the WHERE clause:

select wk_id,
       (floor(td1.military_hour/4)*4) as td1_military_hour_group, 
       (floor(td2.military_hour/4)*4) as td2_military_hour_group
  from task_instances t1
  inner join time_table td1
    on t1.end_time = td1.time_id
  inner join time_table td2
    on t1.start_time = td2.time_id 
  where (floor(td1.military_hour/4)*4) = (floor(td2.military_hour/4)*4)

Or else use a common table expression:

WITH cteTime_groups AS (SELECT time_id,
                               floor(military_hour/4)*4 AS military_hour_group
                          FROM time_table)
select wk_id,
       td1.military_hour_group as td1_military_hour_group, 
       td2.military_hour_group as td2_military_hour_group
  from task_instances t1
  inner join cteTime_groups td1
    on t1.end_time = td1.time_id
  inner join cteTime_groups td2
    on t1.start_time = td2.time_id 
  where td1.military_hour_group = td2.military_hour_group

SQLFiddle here

Upvotes: 1

Related Questions