Take the 2-minute tour ×
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.

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
add comment

2 Answers 2

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"
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
add comment

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"
share|improve this answer
1  
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
add comment

Your Answer

 
discard

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.