php - How do I query two tables in this case? -

for 1 down voted question, please explain me reason? if seems trivial you, appreciate pointing me reference; if duplicate question, appreciate pointing me earlier question. thanks!

i have 2 tables, user , friend. goal follows: user a wants information of user b; returned info depends on whether friends or not. concrete, if friends, a can see b's username , address; if not, a can 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     // 1 (abc) , 2 (def) friends 2    1     3     // 1 (abc) , 3 (ghi) friends 

2 (def) , 3 (ghi) not friends. there following 2 scenarios:

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

is possible using 1 query?

you can use join , check joined user's column exists (is not null) determine if friendship exists.

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

select   user.username,   user.address,   friend.id are_friends user inner join friend   on (friend.id1 = user.id , friend.id2 = 2) user.id = 3; 

this should return row pdo handler if link between user 2 , 3 exists. if doesn't, inner join not work - if used left join here would, return null are_friends field.

of course replace 2 in query current logged in user's id, , replace 3 id of user want information for.