Counting and joining two tables

Posted by Eikern on Stack Overflow See other posts from Stack Overflow or by Eikern
Published on 2010-04-23T12:33:07Z Indexed on 2010/04/23 12:43 UTC
Read the original article Hit count: 663

Filed under:
|
|

Eventhosts – containing the three regular hosts and an "other" field (if someone is replacing them)

eventid | host (SET[Steve,Tim,Brian,other])
-------------------------------------------
      1 | Steve
      2 | Tim
      3 | Brian
      4 | other
      5 | other

Event

id | other | name etc.
----------------------
1  |       | …
2  |       | …
3  |       | …
4  | Billy | …
5  | Irwin | …

This query:

SELECT h.host, COUNT(*) AS hostcount
FROM host AS h
LEFT OUTER JOIN event AS e ON h.eventid = e.id
GROUP BY h.host

Returns

Steve | 1
Tim   | 1
Brian | 1
other | 2

I want it to return

Steve | 1
Tim   | 1
Brian | 1
Billy | 1
Irwin | 1

OR

Steve |       | 1
Tim   |       | 1
Brian |       | 1
other | Billy | 1
other | Irwin | 1

Can someone tell me how I can achieve this or point me in a direction?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about count