Can I combine two MySQL queries into one?

Question

I had the following code

<?php $getFriendQuery = "SELECT DISTINCT U.username FROM users as U INNER JOIN test_friends as F on U.Id = F.user_id WHERE F.friend_id = '{$userID}' && F.active=1"; $getFriendResult = mysql_query($getFriendQuery, $conn) or die (mysql_error()); $friendName = ""; while($getFriendFetch = mysql_fetch_assoc($getFriendResult)){ $friendName .= $getFriendFetch['username'] . ", "; } $getFriendQuery = "SELECT u.username FROM users u INNER JOIN test_friends f ON u.id = f.user_id WHERE (f.friend_id = '{$userID}' AND active=1) OR (f.user_id = '{$userID}' AND active=1)"; $getFriendResult = mysql_query($getFriendQuery, $conn) or die (mysql_error()); while($getFriendFetch = mysql_fetch_assoc($getFriendResult)){ $friendName .= $getFriendFetch['username'] . ", "; } if (!empty($friendName)){ echo "Your friends: " . $friendName ; } else { echo "You do not have any friends yet"; } ?> 

My code needs 2 queries to get a "friends list" . Is there a way to fulfill 1 request and can get a list of all friends?

Additional Information:

I have 2 tables. "Users table" and "test_friends" . User table:

id represents user id => data type int(11)

 username => data type varchar(256) 

Test_friends table has:

user_id represents user id => data type int(11)

 friend_id => data type int(11) active => tinyint 

Answer 1

You should be able to combine to perform both queries in one. Your SQL will look like this:

 SELECT U.username FROM users AS U INNER JOIN test_friends AS F ON U.Id = F.user_id WHERE F.friend_id = '{$userID}' AND F.active = 1 UNION SELECT u.username FROM users u INNER JOIN test_friends f ON u.id = f.user_id WHERE ( f.friend_id = '{$userID}' AND active = 1 ) OR ( f.user_id = '{$userID}' AND active = 1 ) 

It will also automatically delete duplicates, as if you included DISTINCT on the entire lot. (If you do not want this, you do "UNION ALL".)

Also, if you want to order the results, add “ORDER BY 1 ASC” at the end. You can only use the column numbers of the result set in the ORDER BY clause with union.

Union queries only work if the number and types of columns returned as a result given by each subquery are the same.

Beyond this: your first query appears to be a subset of the second query, so you really only need to complete the second query. I left it as a demonstration of how to make alliances, but in this case you really don't need to.

Answer 2

You can execute UNION between two queries. For instance:

 SELECT username FROM users WHERE username like '%billy%' UNION SELECT username FROM users WHERE username like '%bob%' 

will return all users with names like billy or bob. Combining all of your two queries with UNION should work.

Answer 3

Since your first query is a subset of your second query, you only need to complete the second query.

I assume that in the Test_Friends table, the user_id field represents the user ID of the user, and the friend_id field represents the user ID of the user.

If so, you can run the query:

 SELECT DISTINCT U.username FROM Test\_Friends F INNER JOIN Users U ON F.friend\_id = U.user\_id WHERE F.user\_id = '{$userID}' AND F.active = 1 

Answer 4

so you need the names $ userID of friends And the names of users who have $ userID as a friend? What about

 select distinct U.username from users U inner join test_friends f on (f.user_id = U.id AND f.friend_id={userID}) OR (f.friend_id=U.id AND f.user_id={userID}) where active=1