This is my scenario: I have two different tables: Table 'subscriptions' contains all the information related to subscriptions to user sites, and the 'ratings' table, which contains scores to products of subscribing sites. Simplifying is something like that:
Subscriptions: 'id','user','place','created', etc.
Ratings: 'id','user','product','created',etc.
Now I need mysql to return my most recent 10 items, either a subscription or a rating. So far, the solution found was to make two different calls:
SELECT * FROM subscriptions WHERE ... ORDER BY created LIMIT 10.
With the two results (20 elements), I order multidimensional array by 'created' and get the top 10 with (saving the result in $items):
$items = array_slice($items, 0, 10);
I suspect that this solution is not efficient, because I get it in two calls, and return more elemnts than I need. I've been reading that the UNION operator allows you to select several different tables at once, but with the same number of columns. My question is, what is the most efficient way to join a specific number of elements of different types ordered by a common field (created in my case) in just one call? is ot possible? Thanks a lot, any help is appreciated, Jesús.