Complex MySql query help needed

I am trying to find data that may be missing from my database. I have three tables that are part of this. The first is an event table, the second is a sub-event table, and the third is a individual persons' data from a sub-event for a portion of the sub-events.

What I would like to do is: For each event find the sub-events associated with it, if the sub-event has the field tfile set then ignore it, if it doesn't then look at the individual persons' data. If there开发者_JAVA百科 are 0 matching records in that then output to the list.

Here is what I think would work, but isn't right:

select event.*, subevent.* from event.id
join subevent on event.id = subevent.eid
join pdata on subevent.subid = pdata.subid
where subevent.textfile = ''
and Count(pdata.subid) = 0;

Edit: Added code from user, uses real table names:

SELECT tb_event.*, cr_event.* 
FROM tb_event
INNER JOIN cr_event ON tb_event.id = cr_event.eid
INNER JOIN searchable_data ON cr_event.id = searchable_data.race_id
WHERE cr_event.tfile = ''
GROUP BY cr_event.id
HAVING Count(searchable_data.race_id) = 0

Additional information, I believe the issue is around how many items are in the searchable_data. It doesn't seem to find the ones with zero (that I know exist).

as per suggestion of Nick I added columns to the group by

SELECT tb_event.*, cr_event.id 
FROM tb_event
INNER JOIN cr_event ON tb_event.id = cr_event.eid
INNER JOIN searchable_data ON cr_event.id = searchable_data.race_id
WHERE cr_event.tfile = ''
GROUP BY tb_event.id, tb_event.ename, tb_event.ecity, tb_event.eplace, tb_event.elocation, tb_event.zip, tb_event.day, tb_event.year, tb_event.etime, tb_event.logo, tb_event.remail, tb_event.descr, tb_event.link, tb_event.reg, tb_event.rname, tb_event.created_by, cr_event.id
HAVING Count(searchable_data.race_id) = 0

Link to Tables

I have tried for about an hour to get this one query working. Anyone have any other ideas?

---

You are trying to count something that - well, doesn't quite exist because of your INNER JOIN.

You'll need to keep the t/c combinations that doesn't have any matching data in s. See below query to list all t/c id pairs (unique due to GROUP BY) that doesn't have any data in s.

SELECT t.id, c.id 
FROM tb_event t
INNER JOIN cr_event c ON t.id = c.eid
LEFT OUTER JOIN searchable_data s ON c.id = s.race_id

WHERE c.tfile = ''
AND s.race_id IS NULL -- This is what you need to check for
GROUP BY t.id, c.id

---

Try something like:

SELECT tb_event.*, cr_event.id 
FROM tb_event
INNER JOIN cr_event ON tb_event.id = cr_event.eid
INNER JOIN searchable_data ON cr_event.id = searchable_data.race_id
WHERE cr_event.tfile = '' OR cr_event.tfile IS NULL
GROUP BY tb_event.id, tb_event.ename, tb_event.ecity, tb_event.eplace, tb_event.elocation, tb_event.zip, tb_event.day, tb_event.year, tb_event.etime, tb_event.logo, tb_event.remail, tb_event.descr, tb_event.link, tb_event.reg, tb_event.rname, tb_event.created_by, cr_event.id
HAVING Count(searchable_data.race_id) = 0

Report errors/incorrect data it returns.