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