Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

You could convert to an inner join:

select  n.*
from    notes n
join    paragraphs p
on      n.paragraph_id =
where   p.article_id = '111'
order by
share|improve this answer
notes doesn't have an article_id, it only has a paragraph_id – Freewind Mar 29 '12 at 12:44
Right, that should've been p.article_id, answer edited – Andomar Mar 29 '12 at 13:31

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

  paragraps p,
  notes n
  p.article_id = 111
  and n.paragraph_id =
order by p."order"
share|improve this answer
But the questions asks to order by paragraphs.order – Andomar Mar 29 '12 at 9:01
My mistake. :-) thanks for improvement – ipip Mar 29 '12 at 19:14

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.