SELECT phpraid_signups.raid_id , COUNT(*) AS signedup_count, IFNULL( phpraid_raids.max, 0 ) as max
FROM phpraid_signups 
LEFT JOIN phpraid_raids ON ( phpraid_signups.raid_id = phpraid_raids.raid_id)
WHERE cancel =0
AND queue =0
GROUP BY raid_id