You were so very close to getting what you wanted! What is causing the problem is the comma (,) in your FROM clause. MySQL permits two methods of declaring an INNER JOIN. The first is by using the keyphrase "INNER JOIN" the second is with a comma in your table list. Here is how to rephrase your query to return what you wanted:
SELECT C.cId, C.cName, SC.scId, SC.scName, count(T.scId) as tCount FROM C LEFT JOIN SC ON C.cId = SC.cId LEFT JOIN T ON SC.scId = T.scId GROUP BY SC.scId, C.cName, SC.scId, SC.scName ORDER BY C.cId; You also needed to list all of the other un-aggregated columns in your GROUP BY. MySQL will return without an error but EVERY OTHER sql dialect I have used will require those other column names so it is an excellent idea to get into the habit of writing them out every time. Also, since the table SC contains a pointer to C, and T contains a pointer to SC, you don't need to include a pointer to C on T. create table T (tId tinyint(4), scId tinyint(4), topic varchar(50)); (in other words) If a topic belongs to a subcategory, and a subcategory belongs to a category, the topic also belongs to that category. There is no need to restate that in your data. Including a column for the category id on the topic table could actually _create_ a problem if you assigned a topic to a category and to a subcategory that weren't related to each other (the subcategory did not belong to the category). Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Kapoor, Nishikant" <[EMAIL PROTECTED]> wrote on 07/21/2004 03:19:48 PM: > Hello List, > > I have a problem that I am looking your help for. Would greatly > appreciate it. Here is what I am trying to do: > > create table C (cId tinyint(4), cName varchar(10)); > insert into C values (1,'Cat01'), (2,'Cat02'), (3,'Cat03'), (4,'Cat04'); > > create table SC (scId tinyint(4), cId tinyint(4), scName varchar(10)); > insert into SC values (1, 1, 'SubCat01'), (2, 1, 'SubCat02'); > > create table T (tId tinyint(4), scId tinyint(4), cId tinyint(4), > topic varchar(50)); > insert into T values (1, 1, 1, 'Topic01'), (2, 1, 1, 'Topic02'); > > I am using the following sql query: > > SELECT C.cId, C.cName, SC.scId, SC.scName, count(T.scId) as tCount > FROM C left outer join SC on (C.cId = SC.cId), > SC SC1 left outer join T on (SC1.scId = T.scId > and C.cId = T.cId > and SC.scId = T.scId) > GROUP BY SC.scId > ORDER BY C.cId; > > I am expecting to see an output like this: > > cId cName scId scName tCount > ------------------------------------- > 1 Cat01 1 SubCat01 2 > 1 Cat01 2 SubCat02 0 > 2 Cat02 NULL NULL 0 > 3 Cat03 NULL NULL 0 > 4 Cat04 NULL NULL 0 > > BUT, what I am getting is > > cId cName scId scName tCount > ------------------------------------- > 1 Cat01 1 SubCat01 2 > 1 Cat01 2 SubCat02 0 > 2 Cat02 NULL NULL 0 > > i.e. Cat03 & Cat04 do not show up. I don't know whay. I think, I am > missing something obvious. Could any of you please help? > > Thanks, > Nishi > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >