I have the following query, which pulls meet results, articles, and videos from their separate tables, ordered by creation_date, in an effort to display the 10 most recent items. When an item is a meet result, it is linked to differently than videos and articles are linked - that is, articles and videos have a article_path which has the direct URL to link to - meet results have a different way of being linked to, which is why meet_id is being set as path.
Is there a more efficient query? This one appears to me to be working.
Code:
SELECT a.meet_id as path,
' ' as title,
shortened_title as shortened_title,
' ' as intro,
a.meet_picture as image_path,
' ' as youtube_id,
a.pdf as pdf,
date_format(meet_end_date, '%M %D, %Y') AS creation_date
FROM meet_results AS a JOIN meet_result_categories AS c
WHERE a.meet_id = c.meet_id
AND c.category = '1'
UNION
SELECT a.article_id
,article_title as title
,article_shortened_title as shortened_title
,article_intro as intro
,article_picture as image_path
,'none' as youtube_id
,article_path as path
,creation_date
FROM (articles AS a JOIN article_categories AS c ON a.article_id = c.article_id)
WHERE (category = '6' OR category = '8')
UNION
SELECT a.video_id
,video_title as title
,video_shortened_title as shortened_title
,video_intro as intro
,video_picture as image_path
,youtube_id
,video_path as path
,creation_date
FROM (videos AS a JOIN video_categories AS c ON a.video_id = c.video_id)
WHERE category = '9'
ORDER BY creation_date DESC
LIMIT 0,10
dBforums
>
3 Union Statements - Appears to work correctly, but is there a more efficient query?
10-25-10, 22:30
Linear Mode
