SQL expression to get friend names

Question

I use the connect function of friends using SQL. I used the following code:

SELECT relationship.requester, relationship.requested, user.firstname, user.lastname FROM relationship JOIN user ON (user.guid = relationship.requester OR user.guid = sqrelationship.requested) WHERE relationship.requester = {$userid} OR relationship.requested = {$userid} AND relationship.approved = 1 

SQL will return a table with the identifiers of the approved relationships initiated or received by the user. However, user.firstname and user.lastname return me only one set of results. How can I make this expression get both the requestor and the requested names?

Answer 1

It is difficult to say from the question where you do not indicate the design of your tables, but I assume the following:

 select relationship.requester, relationship.requested, requester.firstname, requester.lastname, requested.firstname, requested.lastname, from relationship left join user as requester on user.guid = relationship.requester left join user as requested on user.guid = relationship.requested where relationship.requester = {$userid} and relationship.approved = 1