Get count even if zero is returned

[The Little Guy]

I would like to get a count, even if there is a count of zero.

 

example: I have 2 domains in the database, each domain has zero searches on them, I would still like to get them, but display 0 for the count. Any suggestions on what I should do? I can't seem to think how.

 

select d.domain, count(*) total from domains d left join searches s on(d.domain_id = s.domain_id and d.member_id = 1) group by d.domain_id

 

Thanks for the help!

[joel24]

select d.domain, IF(count(*)=0, 0, count(*)) total from domains d left join searches s on(d.domain_id = s.domain_id and d.member_id = 1) group by d.domain_id

[fenway]

I would like to get a count, even if there is a count of zero.

 

example: I have 2 domains in the database, each domain has zero searches on them, I would still like to get them, but display 0 for the count. Any suggestions on what I should do? I can't seem to think how.

 

select d.domain, count(*) total from domains d left join searches s on(d.domain_id = s.domain_id and d.member_id = 1) group by d.domain_id

 

Thanks for the help!

Are you saying that you don't get back 2 records?

[The Little Guy]

I would like to get a count, even if there is a count of zero.

 

example: I have 2 domains in the database, each domain has zero searches on them, I would still like to get them, but display 0 for the count. Any suggestions on what I should do? I can't seem to think how.

 

select d.domain, count(*) total from domains d left join searches s on(d.domain_id = s.domain_id and d.member_id = 1) group by d.domain_id

 

Thanks for the help!

Are you saying that you don't get back 2 records?

 

Correct. I should get back 2 records, and count should contain 0 for both.

 

@joel24 that doesn't work, as it comes back with a count of 1.

 

I should note that the searches table has 0 records in it.

[The Little Guy]

Here we go:

 

select domain_id, count(s.domain_id) as total, d.is_validated from domains d left join searches s using(domain_id) where member_id = 1 group by d.domain_id;

 

This is what I want.