在这种情况下如何查询两个表？

Question

For the one who down voted the question, could you please explain to me the reason? If this seems trivial to you, I would appreciate you pointing me to a reference; If this is a duplicate question, I would appreciate you pointing me to the earlier question. Thanks!

I have two tables, user and friend. The goal is as follows: A user a wants to get the information of another user b; the returned info depends on whether they are friends or not. To be concrete, if they are friends, a can see b's username and address; if not, a can only see b's username.

user table looks like:

id   username   address
1    abc        XXXX Rd. XXXX
2    def        XXXX Rd. XXXX
3    ghi        XXXX Rd. XXXX

friend table looks like:

id   id1   id2
1    1     2     // so 1 (abc) and 2 (def) are friends
2    1     3     // so 1 (abc) and 3 (ghi) are friends

2 (def) and 3 (ghi) are not friends. So basically there are the following two scenarios:

1. user 2 (def) want to see user 3 (ghi)'s info, the query returns ghi's username only but not address
2. user 1 (abc) want to see user 3 (ghi)'s info, the query returns ghi's username and address

Is it possible to do it using one query?

Answer 1

You can use a JOIN and check that the joined user's column exists (is not null) to determine if the friendship exists.

E.g. if user def (2) wants info on user ghi (3):

SELECT
  user.username,
  user.address,
  friend.id AS are_friends
FROM user
INNER JOIN friend
  ON (friend.id1 = user.id AND friend.id2 = 2)
WHERE user.id = 3;

This should only return a row from your PDO handler if the link between user 2 and 3 exists. If it doesn't, the inner join will not work - if you used a left join here it would, but would return null for the are_friends field.

Of course you would replace 2 in that query with the current logged in user's ID, and replace 3 with the ID of the user that you want information for.