Order by sub select

Question

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.

Answer 1

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"

Answer 2

The inner order by is not necessary at all. May be it also confuses the database, not only me.

select 
  n.*
from 
  paragraps p,
  notes n
where
  p.article_id = 111
  and n.paragraph_id = p.id
order by p."order"