How to use COUNT to get zero values with mysql? -

i read several questions issue on stackoverflow : seems count should used right joining display sums of every items, including ones summing zero.

i'm not able make case, after several hours of headache...

well, have 2 tables. first 1 called "words2", list of words. second 1 called "links2". it's linking 2 words : idword1 , idword2. (there no links linking 2 identical words)

for each word, know how many links used, if there no link.

this query :

select *, count(*) qty   (      select *     words2     left outer join links2 linksa on words2.idword = linksa.idword1       union      select *     words2     left outer join links2 linksb on words2.idword = linksb.idword2 ) tmp iduser = 3 , linktype = 'individual' group word order word 

it works fine unless don't have results unused words, not displayed.

thank help!

to original query change count call count(idword1). cause count number of times idword1 not null. right counting number of rows period 1 should zero.

here's sample dataset:

words2 ------- idword ------- foo bar baz biz buzz  links2 ------- idword1 | idword2 ------- foo     | bar foo     | baz bar     | baz buzz    | foo buzz    | bar 

(this dataset disregards iduser , linktype fields because original question doesn't describe how used , don't appear relevant answer.)

when run query on dataset this:

idword | idword1 | idword2 | linkcount -------------------------------------- bar    | bar     | baz     | 3 baz    | null    | null    | 2 biz    | null    | null    | 1 buzz   | buzz    | foo     | 2 foo    | foo     | bar     | 3 

also note count(*) more expensive depending upon storage engine you're using. see other question details.

when change count count(idword1) this:

idword | idword1 | idword2 | linkcount -------------------------------------- bar    | bar     | baz     | 3 baz    | null    | null    | 2 biz    | null    | null    | 0 buzz   | buzz    | foo     | 2 foo    | foo     | bar     | 3 

here's simpler query uses no subquery , joins words2 links2 using or statement:

select   words2.idword   -- count number of links each word   -- if there no links count() call return 0   , count(idword1) linkcount words2   left join links2     on words2.idword = links2.idword1       or words2.idword = links2.idword2 group words2.idword order words2.idword 

when run on sample dataset following results:

idword | linkcount ------------------- bar    | 3 baz    | 2 biz    | 0 buzz   | 2 foo    | 3