Reuse joined tabled in a sub query

Question

Is there any way we can reuse the reference of table we joined in a sub query?

I have three tables:

• task_categories, information about task categories
• task_priorities, priorities associated with task categories
• task_links, url links for each individual tasks.

Please check this SQL Fiddle.

CREATE TABLE task_categories (
    task_category_id int,
    code varchar(255),
    name varchar(255)
);

CREATE TABLE task_priorities (
    priority_id int,
    task_category_id int,
    priority int
);

CREATE TABLE task_links (
    task_links_id int,
    task_category_id int,
    title varchar(255),
    link varchar(255),
    position int
);

We'd need to join all these tables if we need links of tasks that has high priority. Something like this

select * from task_links t_links
inner join task t on t_links.task_id = t.task_id
inner join task_priorities t_priorities on t.task_id = t_priorities.task_id
where t.code in ('TASK_P2', 'TASK_P3') and
t_priorities.priority = (select min(priority) from task_priorities tp 
                         inner join task t on tp.task_id = t.task_id 
                         where t.code in('TASK_P2', 'TASK_P3'))
order by t_links.position;

Is there any way to optimize this query? This query has joined table twice, I think there should be a better way to write this query.

Answer 1

The logic for your subquery is incorrect. It is not selecting the minimum priority for each task.

I am guessing that you really want:

where t.code in ('TASK_P2', 'TASK_P3') and
      tp.priority = (select min(tp2.priority)
                     from task_priorities tp2
                     where tp2.task_id = t.task_id 
                    )

This doesn't need much more optimization than an index on task_priorities(task_id, priority).