[thelist] Query Question: Order By, Group By, or Sub Query
Nick Daverin
ndaverin at marian.org
Wed May 31 12:38:39 CDT 2006
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.
More information about the thelist
mailing list