Trying to get a list of event types from the database, plus a total of events on the calendar. I needed to have the list of all types, even if the total is 0.
For example:
Meetings 12
Conference Calls 5
Company Parties 0
Vacations 0
So I used a UNION (the first select gives the total events , [Meetings 12 Conference Calls 5], and the other select returns the list of all the event types)
(
SELECT events.events_id, events.name, COUNT( types.event_type )
FROM events, types
WHERE events.events_id = types.event_type
AND (approved = 'Y')
AND (CURDATE( ) <= events.date)
GROUP BY (types.event_type)
ORDER BY name
)
UNION
(
SELECT events.events_id, events.name, NULL
FROM events, types
WHERE events.active = 'Y'
AND events.events_id = types.event_type
)
But I get results like this
Meetings 12
Conference Calls 5
Meetings 0
Conference Calls 0
Company Parties 0
Vacations 0
Can anyone please help?
