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
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