Need Help With Simple MySQL SELECT

I've got a table of crime data. In a simplified version, it would look like this:

Table Headings:

crime_id, neighborhood, offense

Table Data:

 - 1, Old Town,  robbery         
 - 2, Bad Town,    theft        
 - 3, Bad Town,    theft         
 - 4, Uptown,    stolen auto

If I SELECT * FROM mytable WHERE offense ='theft', then the results for Bad Town are returned. Bu开发者_Go百科t, I'm making a ranking, so what I'm really interested in is:

Old Town: 0  
Bad Town: theft  
Bad Town: theft  
Uptown:   0

How do I write a SELECT statement that returns cases where there are thefts, but also returns neighborhoods that don't have an entry for the specified offense?

UPDATE: This my actual SELECT. I'm having problems applying the solution that p.campbell and Gratzy were so kind to post to this SELECT. How do I apply the CASE statement with the COUNT(*)?

SELECT 
        cbn.neighborhoods AS neighborhoods, 
        COUNT(*) AS offenses, 
        TRUNCATE(((na.neighborhood_area_in_sq_meters /1000) * 0.000386102159),2) AS sq_miles,  
        ( COUNT(*) / ((na.neighborhood_area_in_sq_meters /1000) * 0.000386102159) ) AS offenses_per_sq_mile 
        FROM 
            wp_crime_by_neighborhood cbn, wp_neighborhood_area  na
        WHERE 
            cbn.offense='theft' 
            AND 
            cbn.neighborhoods = na.neighborhoods
        GROUP BY 
            cbn.neighborhoods 
        ORDER BY 
            offenses_per_sq_mile DESC

---

If you're looking to make a ranking, wouldn't it be better to get the number of thefts in Bad Town rather than a row for each? Something like this:

select distinct mt.neighborhood, ifnull(total, 0)
from mytable mt
left join (
  select neighborhood, count(*) as total
  from mytable
  where offense = 'theft'
  group by neighborhood
) as t on t.neighborhood = mt.neighborhood

Based on the data you gave, this query should return:

Old Town: 0
Bad Town: 2
Uptown: 0

That seems more useful to me for making a ranking. You can easily throw an order by on there.

---

I would think using a case statement should do it.

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

something like

Select neighborhood, 
case offense when 'theft' then offense  else '0' end case 
from table

---

Try this:

SELECT  cbn.neighborhoods AS neighborhoods,  
    CASE WHEN IFNULL(COUNT(*),0) > 0 THEN CONCAT(COUNT(*), ' ', offense)
         ELSE  '0' 
    END AS offenses
    --- ... and the rest of your query

    FROM        wp_crime_by_neighborhood cbn
    INNER JOIN  wp_neighborhood_area  na  
        ON      cbn.neighborhoods = na.neighborhoods 
    WHERE       cbn.offense='theft'                     
    GROUP BY    cbn.neighborhoods  
    --ORDER BY  offenses_per_sq_mile DESC