1

Wasn't sure how to word my question, so sorry if it's a little confusing. Hopefully the below will clear it up a bit.

Here's the query I'm running so far:

select business.name, ifnull(sum(checkin.count),0) as checknum from checkin inner join business on business.id = checkin.business_id where business.state = 'NY' group by business.name order by checknum desc;

and it almost works as intended. There is one more business in NY, which doesn't have any counts in the checkin table, but I can't get it to output.

This is my current output:

+---------------------------------------+----------+ | name | checknum | +---------------------------------------+----------+ | Lakeside Coffee | 31 | | McDonalds | 18 | | Valero | 15 | | Angelo's Pizza & Grill | 13 | | Gino's Pizza | 8 | | Sandy's Deli | 7 | | Tribeca North Luxury Apartments Homes | 2 | | Matt & Nat | 2 | | Filion's Diner | 2 | | Squirrel's Nest Restaurant and Bar | 2 | | Best Friends Family Diner | 1 | | China Buffet | 1 | | Sandi's Kountry Kitchen | 1 | | Dick's Country Store | 1 | +---------------------------------------+----------+

But I need to capture the last business that doesn't have any checkins. Not sure how to tackle this further.

jrod091
  • 51
  • 7
  • 1
    Is this what you mean? https://stackoverflow.com/questions/7602271/how-do-i-get-sum-function-in-mysql-to-return-0-if-no-values-are-found – Andy Sep 28 '17 at 14:58
  • Possible duplicate of [How to include "zero" / "0" results in COUNT aggregate?](https://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate) – isaace Sep 28 '17 at 15:01
  • thanks for that @isaace I wasn't sure how to search my question :) – jrod091 Sep 28 '17 at 15:14

1 Answers1

1

You need to use right join instead of inner join.

Left join will return all rows on the right side of the relationship regardless of whether any key on the left side matches.

Kevin Stricker
  • 17,178
  • 5
  • 45
  • 71
  • thanks for the input! I actually need to do a `right join` since the business table has the extra business and is being joined in, right? – jrod091 Sep 28 '17 at 15:02
  • Just a suggestion though, it's a fairly common convention to start with the parent table and use left joins. Makes things easier to read/follow. – Kevin Stricker Sep 28 '17 at 15:08