Simplifying JOIN with multiple 'right' items

[ManiacalV]

I will include the fun stuff below but here's the gist of my question.

 

I have a table 'pages' that is the main information for an editable page.

 

There will be a list of links and ids for people who can edit each page, and these are in tables link_list and editors.

 

When I do the following, I get as many results as there are links and editors.

 

SELECT pa.page_id, pa.page_title, pa.page_desc, 
ll.ll_name, ll.ll_url, ll.ll_target, ll.ll_order
ed.ed_staff_id 
FROM main_content.pages pa 
LEFT JOIN main_content.link_list ll ON pa.page_id = ll.ll_page_id 
LEFT JOIN main_content.editors ed ON pa.page_id = ed.ed_page_id 
WHERE pa.page_id='1'

 

I know I can kind of 'fudge' it using GROUP CONCAT, but it there a more elegant solution for returning each item for 'page' with an array for each of the sub-items associated with it using one call?

 

Mysql 5.0.51

 

CREATE TABLE `pages` (
  `page_id` int(10) unsigned NOT NULL auto_increment,
  `page_parent` int(10) unsigned NOT NULL default '0',
  `page_title` varbinary(128) NOT NULL,
  `page_desc` blob,
  `page_changed` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `page_staff_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`page_id`)
) 

 

CREATE TABLE `link_list` (
  `ll_page_id` int(10) unsigned NOT NULL,
  `ll_name` varbinary(40) NOT NULL,
  `ll_url` varbinary(255) NOT NULL,
  `ll_target` varbinary(32) NOT NULL,
  `ll_order` int(11) NOT NULL,
  `ll_changed` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ll_staff_id` int(10) unsigned NOT NULL,
  KEY `ll_page_id` (`ll_page_id`),
  KEY `ll_name` (`ll_name`),
  KEY `ll_url` (`ll_url`)
)

 

CREATE TABLE `editors` (
  `ed_page_id` int(10) unsigned NOT NULL,
  `ed_staff_id` int(10) unsigned NOT NULL,
  `ed_changed` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ed_changed_staff_id` int(10) unsigned NOT NULL,
  KEY `ed_page_id` (`ed_page_id`),
  KEY `ed_staff_id` (`ed_staff_id`)
)

[ManiacalV]

Let me rephrase the question if this helps. I can do what I want in 3 separate mysql calls:

 

Each page has one ID so I get the page info with this:

 

SELECT pa.page_title, pa.page_desc FROM main_content.pages pa WHERE pa.page_id='id_variable'

 

Each page can have multiple links so I get them here:

 

SELECT ll_name, ll_url, ll_target, ll_order FROM main_content.link_list ll WHERE ll_page_id = '"id_variable"' ORDER BY ll_order

 

And each page can have multiple people who can edit (who are listed in another database by ID):

 

SELECT ed.ed_staff_id, CONCAT(sm.sm_last_name, ', ', sm.sm_last_name) as name FROM main_content.editors ed LEFT JOIN staff_v2.staff_members sm ON ed.ed_staff_id = sm.sm_id WHERE ed_page_id = 'id_variable'

 

These work just fine. I'm just curious if there's a way to make this work with only one call...