Selecting exactly one item from each condition of the WHERE clause

This is my current query:

SELECT 
  topic.title, 
  topic.content_id, 
  topic.thumbnail_icon, 
  text.description 
FROM 
  mps_contents AS topic, 
  mps_contents AS text 
WHERE 
  topic.featured = '1' AND 
  topic.active = '1' AND 
  topic.page_id = (SELECT page_id 
                     FROM mps_pages 
                    WHERE page_short_name = 'services' 
                       OR page_short_name = 'questions_faqs') AND 
  text.section_id = (SELECT section_id 
                       FROM mps_sections 
                      WHERE section_short_name = 'page_text') AND 
  text.page_id = topic.content_id AND 
  topic.resource_type = 'subpage' AND 
  text.resource_type = 'text' 
ORDER BY RAND() LIMIT 2

ED开发者_StackOverflow中文版IT:

This is MySQL

What I want to do really is select 1 featured item from services and 1 featured item from questions. However this query will select 2 featured items from either services or questions!

I know that one option is to make a UNION and doing LIMIT 1 on both queries of the UNION but that would mean I would have to duplicate 90% of the query. Can I do this without having to resort to UNIONs?

well, I believe unless you re-design/normalize the table, you will always end up repeating the query to some extent.

If all you want to avoid union, you can re-write

  topic.page_id = (SELECT page_id 
                 FROM mps_pages 
                WHERE page_short_name = 'services' 
                   OR page_short_name = 'questions_faqs')

to

topic.page_id = (SELECT page_id 
                 FROM mps_pages 
                 WHERE page_short_name = 'services' ORDER BY RAND() LIMIT 1)
                   OR 
topic.page_id = (SELECT page_id 
                 FROM mps_pages 
                 WHERE page_short_name = 'questions_faqs' ORDER BY RAND() LIMIT 1)

I don't think there will be too much performance boost though as rand() run twice