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

[Nick Daverin]

This may work a little better for you...

SELECT a.ID, b.parentID, a.title as heading, b.title as subheading
FROM links as a
LEFT JOIN links as b on b.parentID = a.ID
WHERE a.parentID IS NULL
ORDER BY a.title,b.title;

This will return your links as such:

 a.ID         b.childID           a.heading       b.subheading
 
 --------------------------------------------------------------
 
 1               2                 About           History

 1               4                 About           Our People
 
 3                                 Contact

Now you can take parent titles from 'heading' and sublinks from 'subheading'
The links will be sorted alphabetically.

-Nick




On 5/31/06 12:38 PM, "Matt Warden" <mwarden at gmail.com> wrote:

> 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.