Counting and joining two tables

Question

Eventhosts – containing the three regular hosts and an "other" field (if someone is replacing them) or edit: being a guest host (in addition to the regulars)

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

Event

id | other | name etc.
----------------------
1  |       | …
2  |       | …
3  |       | …
4  | Billy | …
5  | Billy | …
6  | 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 | 2
other | 2

I want it to return:

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

OR:

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

And not:

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

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

Answer 1

Just remove the GROUP BY (since you don't want it collapsing values for that column) and add the event.other column to the column list.

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

I just remembered you could achieve the first solution as well by:

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

Answer 2

SELECT eh.host, e.other, count(*)
FROM Eventhosts eh
LEFT JOIN Event e ON (eh.eventid = e.id)
GROUP BY eh.host, e.other

returns

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

Answer 3

Use this:

SELECT IF(h.host != 'other', h.host, e.other) as the_host, COUNT(e.*) AS hostcount
FROM host h
LEFT JOIN event e ON h.eventid = e.id
GROUP BY the_host

Just a note, don't use COUNT(*), if a host don't have event, it will show 1 instead of 0. Use COUNT(e.*)

For the last result, use this:

SELECT h.host, e.other, COUNT(e.*) AS hostcount
FROM host h
LEFT JOIN event e ON h.eventid = e.id
GROUP BY IF(h.host != 'other', h.host, e.other),
   h.host, e.other -- try repeating it here

[EDIT]

Tried the following query(i.e. without the suggested repetition of fields on GROUP BY), it also works on your original question and your edited question. I just installed MySQL now, I don't know if it has bearing on database type, I only enabled InnoDB and strict settings. By the way COUNT(e.*) (which is ANSI SQL-accepted I presume) doesn't work on MySQL, instead must use COUNT(e.id) (or maybe you already amended in your query):

SELECT h.host, e.other, COUNT(e.id) AS hostcount
FROM host h
LEFT JOIN event e ON h.eventid = e.id
GROUP BY IF(h.host != 'other', h.host, e.other)
   -- note: i removed the suggested repeating of fields here, it works on my box here.     
   -- Settings: InnoDB and strict mode