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]
