complicated SQL question

[lkrubner@geocities.com]

Pardon my lack of SQL knowledge. If I have two tables like this:

weblogs{
id
title
mainContent
}

entries{
id
title
mainContent
belongsToWhichW eblog
}


The last field in entries holds the id of the weblog to which the entry
belongs.

Suppose I want to select the id and title of the weblogs, but I want my
ORDER BY clause to somehow say that the weblogs should be ordered by
how many entries belong to that weblog. If there are two weblogs with
ids 4 and 54 and 4 shows up in belongsToWhichW eblog 23 times and 54
only shows up 12 times, then I want the weblog with the id of 4 to come
first in the ordering.

Is there a way to do that? Mind you, I've a somewhat new version of
MySql in the 3.x series installed, but for various reasons I can not
upgrade to version 4.

[Tim Van Wassenhove]

Re: complicated SQL question

On 2005-01-28, lkrubner@geocit ies.com <lkrubner@geoci ties.com> wrote:[color=blue]
> Pardon my lack of SQL knowledge. If I have two tables like this:
>
> weblogs{
> id
> title
> mainContent
> }
>
> entries{
> id
> title
> mainContent
> belongsToWhichW eblog
> }[/color]

If i'm not mistaken it should go like this:

SELECT w.id AS id, w.title AS title, IF(ISNULL(e.id) , 0, COUNT(*)) AS count
FROM weblogs AS w
LEFT JOIN entries AS e ON w.id=e.belongsT oWhichWeblog
GROUP BY w.id
ORDER BY count ASC";


--
Met vriendelijke groeten,
Tim Van Wassenhove <http://www.timvw.info>

[Geoff Berrow]

Re: complicated SQL question

I noticed that Message-ID:
<1106934640.803 924.309260@c13g 2000cwb.googleg roups.com> from
lkrubner@geocit ies.com contained the following:
[color=blue]
>Suppose I want to select the id and title of the weblogs, but I want my
>ORDER BY clause to somehow say that the weblogs should be ordered by
>how many entries belong to that weblog. If there are two weblogs with
>ids 4 and 54 and 4 shows up in belongsToWhichW eblog 23 times and 54
>only shows up 12 times, then I want the weblog with the id of 4 to come
>first in the ordering.
>
>Is there a way to do that?[/color]

Probably some SQL wizard will come up will an elegant solution, but one
way would be to Select all weblogs to get the ids then for each weblog
id, query the database to see how many entries each has
(mysql_numrows( )) Store that information in an array. Sort the array
and Robert is your Mother's sibling.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[lkrubner@geocities.com]

Re: complicated SQL question

Thanks much for the help. I think I understand everything you've
written except for this part:

IF(ISNULL(e.id) , 0, COUNT(*))

What does that accomplish? If id in Entries in auto_increment then is
this necessary? I assume the if() clause is there to protect against
some possible situtation, but I'm wondering what that situation is.

[Tim Van Wassenhove]

Re: complicated SQL question

On 2005-01-28, lkrubner@geocit ies.com <lkrubner@geoci ties.com> wrote:[color=blue]
> Thanks much for the help. I think I understand everything you've
> written except for this part:
>
> IF(ISNULL(e.id) , 0, COUNT(*))
>
> What does that accomplish? If id in Entries in auto_increment then is
> this necessary? I assume the if() clause is there to protect against
> some possible situtation, but I'm wondering what that situation is.[/color]


-> left join from weblog on entries

In the case there is a weblog without related entries

-> attributes for entries will be null
-> count(*) returns 1

But we want it to be 0

--
Met vriendelijke groeten,
Tim Van Wassenhove <http://www.timvw.info>