Limit result of two different SQL queries

Question

When you have a table you can limit the amount of results. But I have two tables:

This is the first one:

$requests_q = mysql_query("SELECT ava_users.*
FROM ava_friend_requests
LEFT JOIN ava_users 
ON ava_friend_requests.from_user = ava_users.id
WHERE ava_friend_requests.to_user = $user[id]");

This is the second one:

$friends_q = mysql_query("SELECT ava_users.*
FROM ava_friends
LEFT JOIN ava_users 
ON ava_friends.user2 = ava_users.id
WHERE ava_friends.user1 = $user[id]");

This is the table definition for ava_friend_requests:

Field       Type                    Null    Key     Default     Extra
id          int(11) unsigned        NO      PRI     NULL        auto_increment
from_user   int(11)                 YES     MUL     NULL    
to_user     int(11)                 YES     MUL     NULL    

This is the table definition for ava_friends:

Field   Type    Null    Key     Default     Extra
user1   int(11) NO      MUL     NULL    
user2   int(11) NO      MUL     NULL

I'd like to limit the results in a way as if there was no second query (i.e. two queries but ONE LIMITED RESULT ONLY). I was searching the net but couldn't find anything so far. I'm not sure if UNION can help me in this case.

Answer 1

Try something like this:

$union_q = mysql_query(
    "SELECT ava_users.*
     FROM ava_friend_requests
     LEFT JOIN ava_users 
     ON ava_friend_requests.from_user = ava_users.id
     WHERE ava_friend_requests.to_user = $user[id]
     UNION ALL
     SELECT ava_users.*
     FROM ava_friends
     LEFT JOIN ava_users 
     ON ava_friends.user2 = ava_users.id
     WHERE ava_friends.user1 = $user[id]
     LIMIT 0, 10");

UPDATE: As pointed out by Sylvain Leroux in the comments, the order of results returned by the union isn't guaranteed to be all those from the first query followed by all rows from the second query. If this is an issue for you, would suggest modifying the query to order the records, e.g as follows:

SELECT ava_users.*, 0 AS ord
FROM ava_friend_requests
LEFT JOIN ava_users 
ON ava_friend_requests.from_user = ava_users.id
WHERE ava_friend_requests.to_user = $user[id]
UNION ALL
SELECT ava_users.*, 1 AS ord
FROM ava_friends
LEFT JOIN ava_users 
ON ava_friends.user2 = ava_users.id
WHERE ava_friends.user1 = $user[id]
ORDER BY ord
LIMIT 0, 10

(If you don't want to include the ord field in the SELECT, it's possible by making the whole thing into a subquery but that'd make it more complex and is probably getting outside the scope of the original question.)