On Sun, 21 Mar 2004 02:38:01 GMT, "Alexander Ross"
<alexross@bleen.net> wrote:
[color=blue]
>Lets say I have 2 tables, tbl_topics and tbl_lessons:
>
>
>tbl_topics
>
> topicID
> INT
>
> parentID
> INT
>
> Title
> VARCHAR
>
>
>
>tbl_lessons
>
> lessonID
> INT
>
> topicID
> INT (FK)
>
> Other fields
>
>
>
>
>I have a query that gives me all the topics who's parentID = $whatParentID
>:
>
>"SELECT topicID FROM tbl_topics WHERE parentTopicID=$whatTopicID"
>
>
>Now I want to modify this query so I get a second column called count that
>contains the number of times the returned topicID is found in tbl_lessons
>even if that count is 0.
>
>
>Example result:
>
> topicID
> Count
>
> 1000
> 2
>
> 1003
> 0
>
> 1021
> 4
>
>
>
>In this example, topics 1000,1003,and 1021 all have a parentID of 1009 (or
>whatever) and 1000 has 2 lessons, 1003 has 0, and 1021 has 4.
>
>
>Anyone? Anyone? Bueller? Bueller?
>[color=green]
>>"[/color]
>
>BAD JOKE: (read this outloud)
>
>Have you heard about circus sex? its in tents.[/color]
SELECT t.topicID, c.count FROM tbl_topics t inner join (select
topicID, count(1) from tbllessons group by topicID) c on c.topic_id =
t.topic_id WHERE t.parentTopicID=$whatTopicID"[color=blue]
>
>
>
>[/color]
mysql question and a really bad joke 
