Mysql request SELECT with LEFT JOIN

Question

I am a beginner in MySQL, I had some courses in mysql that are now finished, I have a homework , provided by our teacher, to make for my training but I block to retrieve data from the base for a social network site. I understand the basis of JOIN but I still have trouble understanding the logic of LEFT JOIN OR OTHER type INNER JOIN ...

Here is my problem, I have a database with 2 tables,

member(id_member*, login, photo)
friend(id_member_request*, id_member_accept*, accept, date_acceptation)

The accept field of the friend table is a field that allows me to validate if they have friends by setting the value to 1 instead of 0.

The fields id_member_request and id_member_accept agree to the id_member of the member table.

I want to retrieve the login and the picture of the members who are friends, to be able to display them then.

I tested several queries:

SELECT m.login
     , m.photo 
  FROM friend AS a 
  LEFT 
  JOIN member AS m 
    ON m.id_member = a.id_member_accept 
  LEFT 
  JOIN member AS m1 
    ON m1.id_member = a.id_member_request 
 WHERE accept = 1;

The query works but does not show all friendly members and even several times some people.

In RIGHT JOIN The result is NULL. In INNER JOIN no result.

Thanks in advance because I blocked for several hours and I confess to lose myself a little. ;-)

Answer 1

Formatting the statement will help you :) Okay, so you are joining the same column on both m and m1.

The following will bring back the data, but you have to pass in id_member_request..

DECLARE INT @MEMBERID = 2; --Example ID

SELECT m.login, m.photo 
FROM friend AS a 
LEFT JOIN member AS m ON m.id_member = a.id_member_accept
WHERE a.id_member_request = @MEMBERID
   AND a.accept = 1;

This is bring back the m.login, m.photo for all of the friends for the Member with the ID @MEMBERID;