Hi,

Just getting into the murkier depths of MySQL and trying to optimise use of queries, and this one has me stuck.

I have three tables linked via a couple of relation tables that describe the structure of a site. I'd like to do a single query to grab out all of the data I need for a page which will describe sections, the pages within those, and the content within each section, keeping in mind that some sections won't have pages yet, and so on... The obvious solution is a series of left outer joins, and this gives me exactly what I need:

SELECT structure_section.*,structure_page.*,content.* FROM structure_section
LEFT OUTER JOIN structure_section_page ON (structure_section_page.SectionID=structure_sectio n.SectionID)
LEFT OUTER JOIN structure_page ON (structure_page.PageID=structure_section_page.Page ID)
LEFT OUTER JOIN structure_page_content ON (structure_page_content.PageID=structure_page.Page ID)
LEFT OUTER JOIN content ON (content.ContentID=structure_page_content.ContentI D) LIMIT 11

The problem is that the LIMIT applies to the entire result set, but what I really need is to limit it to the first 11 distinct entries on the very left table (structure_section), with all results from the tables to the right.

Obviously I could do this with a series of subsequent queries on the returned data, but the idea is to do it in a single query and prevent potentially hundreds of calls. Is this possible? Or will I have to return the entire result set and let my code deal with the limits? (not a preferred option, obviously!)

I have a feeling the answer may be in subqueries, but that's just a hunch...

While there are many solutions, the one with the most "MySQL flavor" would be to create a temporary table with only the first N rows from the table you wish to limit. Then use that temporary table for your JOIN.

-PatP