Order by sub select

order-bysubquery

I have 3 tables: articles, paragraphs, notes

An article may have many paragraphs, and a paragraph may have 0 or 1 note.

Now I want to find all notes belong to an article:

select * from notes 
where paragraph_id in 
   (select id from paragraphs where article_id='111' order by "order" asc)
order by ???

It can get all notes for article 111, but the order is not correct. I want the items in the result set have the same order as the order of id in the sub select.

But how? I have tried for a while, but not get the correct solution.

Best Answer

You could convert to an inner join:

select  n.*
from    notes n
join    paragraphs p
on      n.paragraph_id = p.id
where   p.article_id = '111'
order by
        p."order"
Related Question