getting a query column to return 0 if nothing matches

I have a query like this (this one is simplified to illustrate my question):<br /><br /> select<br />         l.email,
p.practice_name,count(lp.provider_key) as patient_count<br /> from<br />         login l, provider p, login_provider
lp<br/> where<br />         p.login_key = <a href="http://l.id">l.id</a><br /> and<br />         lp.provider_key =
p.provider_key<br/> group by<br />         l.email, p.practice_name<br /><br /> It works fine but only returns rows
wherethe provider key appears in the login_provider table.<br /> I want to show all rows where the first part of the
WHEREclause is true, regardless of whether the provider_key is in the login_provider table (in that cause, I want to
show0 in that column).<br /><br /> In other words, currently if I run the above query, it will return 19 rows, and if I
runthis one:<br /> select<br />         l.email, p.practice_name<br /> from<br />         login l, provider p<br />
where<br/>         p.login_key = <a href="http://l.id">l.id</a><br /><br /> I get 57 rows.  What I want is for the
firstquery to also return 57 rows and just show 0 for patient_count if the second part of the where clause cannot be
satisfied.<br/><br /> I'm sure this is a simple thing, but I'm not sure how do do it. Can anyone help?<br /> Thanks<br
/>