Paginate而忽略子行，右键加入

I have this basic query that returns a main row of a program title(eventdesc) and then lists program participants and info as multiple sub rows.

PROGRAM 1
Email 1, email 2, name, status (first participant)
Email 1, email 2, name, status (second participant)
PROGRAM 2
Email 1, Email 2, name, status (first participant)

ETC...

Basic query:

$query_perpage = "SELECT COUNT(*) FROM camps_events";
$result = mysql_query($query_perpage, $db) or die(mysql_error());
$r = mysql_fetch_row($result);
$numrows = $r[0];


$pages = new Paginator($query);
$pages->items_total = $numrows;
$pages->paginate();
$pages->mid_range = 7;


$query = mysql_query("SELECT ce.eventcode, ce.eventdesc, ce.date, r.participant_fname, r.participant_lname, 
r.position, r.dob, r.status, r.email_primary, r.order_number 
FROM camps_events ce 
RIGHT JOIN registrations r on r.eventcode = ce.eventcode 
WHERE ce.reg_status = 'Active' 
AND r.status NOT IN ('Incomplete','Canceled')".getAllowedPrograms()." 
ORDER BY ce.eventdesc, ce.eventcode, r.participant_lname ASC $pages->limit ") or die(mysql_error());

I've been trying to figure out how to paginate it by Program, no matter how many subrows. I have a pagination class in place on another page, but that only pulls from one table.

I've been trying to implement the same pagination, but I can only get it to pull the first program, and then it limits the page based on the sub rows, not the main program row. And in the state I've posted it here, each following page displays only the first programs. The sub rows that come up are different on each page, but I can't tell if they are all in the first program.

The query works fine for pulling all results properly, but if it's a huge list, it's extremely inefficient, and can even timeout the browser.

Any help would be much appreciated, please let me know if I can include anything else.

I'm not too concerned with using the same pagination class, so I'm completely open to different approaches.

Thanks in advance

Try using a subselect:

SELECT ce.eventcode, ce.eventdesc, ce.date, r.participant_fname, r.participant_lname, 
    r.position, r.dob, r.status, r.email_primary, r.order_number 
FROM camps_events ce 
RIGHT JOIN registrations r on r.eventcode = ce.eventcode 
WHERE ce.reg_status = 'Active' 
    AND r.status NOT IN ('Incomplete','Canceled')".getAllowedPrograms()." 
    AND ce.id in (select id from camps_events pgce order by ce.eventdesc, ce.eventcode $pages->limit)
ORDER BY ce.eventdesc, ce.eventcode, r.participant_lname ASC ") 
or die(mysql_error());

Edit: unfortunately MySQL can not yet limit subqueries. :-( So it has to be a temp-table:

mysql_query("drop temporary table if exists tmp_events", $db);
mysql_query("create temporary table tmp_events select id from camps_events order by eventdesc, eventcode " . $pages->limit, $db);

mysql_query("SELECT ce.eventcode, ce.eventdesc, ce.date, r.participant_fname, r.participant_lname, 
    r.position, r.dob, r.status, r.email_primary, r.order_number 
FROM camps_events ce 
inner join tmp_events pgce on pgce.id = ce.id
RIGHT JOIN registrations r on r.eventcode = ce.eventcode 
WHERE ce.reg_status = 'Active' 
    AND r.status NOT IN ('Incomplete','Canceled')".getAllowedPrograms()." 
ORDER BY ce.eventdesc, ce.eventcode, r.participant_lname ASC ") 
or die(mysql_error());

There can be of course pages with less than paged items, because events without registrations will not show.