Ushahidi Export Query

gistfile1.sql

SELECT incident.id as "#", incident_title AS "INCIDENT TITLE", incident_date AS "INCIDENT DATE", location.location_name AS LOCATION, incident_description AS DESCRIPTION, category.category_title AS CATEGORY, location.latitude AS LATITUDE, location.longitude AS LONGITUDE, CASE WHEN incident_active = 1 THEN "YES" ELSE "NO" END AS APPROVED, CASE WHEN incident_verified = 1 THEN "YES" ELSE "NO" END AS VERIFIED

FROM incident INNER JOIN location ON incident.location_id = location.id LEFT JOIN incident_category ON incident.id = incident_category.incident_id LEFT JOIN category ON incident_category.category_id = category.id

GROUP BY incident.id

ORDER BY incident.id ASC;