[thelist] Query Question: Order By, Group By, or Sub Query

[Matt Warden]

On 5/31/06, Casey Crookston <caseyc at intellisoftmn.com> wrote:
> I was not very clear.  The titles could be anything and we can't use
> them for the sort:
>
> > ID         Parent ID           Title
> >
> > ----------------------------------------------
> >
> > 1                              About
> >
> > 2          1                   History
> >
> > 4          1                   Our People
> >
> > 3                              Contact
>
> What we need to do is first return all rows where there is no parent id
> (thus a top level link) BUT under each top level link we need to return
> any rows where parented = ID

Ah, I see. I knew that was too easy :)

Something like this:

select *
from mytable a
left outer join mytable b on b.parentid=a.id
order by a.id, b.id

should get you this:


1   null   Title1   null  null   null
1   null   Title1   2     1      Title1a
1   null   Title1   4     1      Title1b
2   1      Title1a  null  null   null
3   null   Title2   null  null  null
4   1      Title1b  null  null   null

Might be a little off (gotta head to lunch), but hopwfully that heads
you in the right direction.

-- 
Matt Warden
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.