2

I tried looking around for similar solutions but it didn't seem to help much in my case. I have a table as such:

locations => |longname|name|locations|

topics    => |longname|name|tag|

and I am trying to get the count of longnames with a specific atag for a particular location. The query I have so far also tries to force reordering, which is why you see the location IN and FIELD. However, if there aren't any people in a location for a particular tag, it omits it entirely from the response. I'd like it to return 0. How can I do that?

select count(b.longname) as count, a.location 
 from `topics` b, locations a 
  where tag = 'atag' and a.longname = b.longname 
  and location IN ('Japan', 'France', 'Italy') 
  group by location 
  order by FIELD(location, 'Japan', 'France', 'Italy')
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Rio
  • 14,182
  • 21
  • 67
  • 107

1 Answers1

4

Perhaps this? :

SELECT COUNT(t.longname) as cnt, l.location 
FROM locations l 
  LEFT JOIN topics t
    ON  t.longname = l.longname
    AND t.tag = 'atag'      
WHERE l.location IN ('Japan', 'France', 'Italy') 
GROUP BY l.location 
ORDER BY FIELD(l.location, 'Japan', 'France', 'Italy')
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • This worked. Seems awfully convoluted, no? I'm glad it works! – Rio Jul 06 '11 at 20:41
  • @ypercube: Brainfart on my part - +1 to you for catching it. – OMG Ponies Jul 06 '11 at 20:42
  • @Rio: This problem is quite common. Search for `OUTER JOIN / INNER JOIN differences` to see why this happens and how you can avoid it. (Very) short explanation here: http://stackoverflow.com/questions/6527608/problem-constructing-where-clause-to-include-null-0-values/6527637#6527637 – ypercubeᵀᴹ Jul 06 '11 at 20:53
  • And you should really not use `WHERE` for joining tables. – ypercubeᵀᴹ Jul 06 '11 at 20:55
  • 1
    @Rio: No, it's not about performance, it's about code maintenance and readability. In a JOIN with 10 tables , having the (9) join conditions in the `WHERE`, along with several other conditions makes the query quite hard to read. If a join condition is missing is not easy to spot, too. See the answers here (if you search SO, you'll find a lot of other discussions and comments about this): http://stackoverflow.com/questions/128965/ – ypercubeᵀᴹ Jul 06 '11 at 21:04