Noré_the_lucky Noré_the_lucky -4 years ago 86
MySQL Question

Mysql request SELECT with LEFT JOIN

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 Source

I think you want the friends of person who logged in. means for particular Member_Id here is query that will help you

 select * from friend a
 inner join member b on (a.id_member_request=b.id_member or a.id_member_accept=b.id_member) and b.id_member=1 
where accept=1

where 1 is a member id whose friends will be displayed

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download