在一个查询中加入/查看Mysql中的三个表(Join/Look In Mysql Three Tables In One Query)

 我决定创建人们你可能知道的系统，但我已经解决了一个问题，似乎我需要加入三个表来带来相关的结果。 
 
 目的是在数据库表中检查可能根据我朋友的朋友知道的朋友(if user1 = user2 and user2 = user4 posibile user1 knows user4, user4 is a suggestion)我使用以下代码来检查这个并且是按计划工作。 
 
SELECT `friendpin` AS `possible_friend_id`
FROM `pinme`
WHERE `senderpin`
IN (
SELECT `friendpin`
FROM `pinme`
WHERE `senderpin` = $userlogged
)
AND `friendpin` NOT
IN (

SELECT `friendpin`
FROM `pinme`
WHERE `senderpin` = $userlogged
)
AND NOT `friendpin` = $userlogged
GROUP BY `possible_friend_id`
ORDER BY COUNT( * ) DESC
LIMIT 0 , 3 
 
 因此， REQUEST表中的建议人员状态可能已经向您提出待处理请求，但仍然显示为建议的朋友，或者在BLOCKED表中，建议的朋友被阻止但仍然显示给您作为建议的朋友。 因此我想消除这种行为。 建议的人不应该在REQUEST表或BLOCKED表中退出。 
 
 谁能帮我解决这个问题？ 
 
 以下是REQUEST表， PINME表和BLOCKED表结构 
 
REQUEST TABLE:
senderpin - id of the person who send request   
friendpin - receivers request id    


BLOCKED TABLE: 
user1_id - a person who block user2_id
user2_id - the blocked person by user1_id


PINME TABLE:
PINME table: the table which keep relations
structure: 1,2 2,2 | 4,8 8,4
senderpin - id of the person who send request   
friendpin - receivers request id
 
 感谢致敬 

I decided to create People You May Know System but i have accounted a problem where it seems I need to join three tables to bring relevant results.
 
The aim is to check in database table for possible friends one may know based on friend of my friend (if user1 = user2 and user2 = user4 posibile user1 knows user4, user4 is a suggestion) I have used the following code to check this and is working as to plan.
 
SELECT `friendpin` AS `possible_friend_id`
FROM `pinme`
WHERE `senderpin`
IN (
SELECT `friendpin`
FROM `pinme`
WHERE `senderpin` = $userlogged
)
AND `friendpin` NOT
IN (

SELECT `friendpin`
FROM `pinme`
WHERE `senderpin` = $userlogged
)
AND NOT `friendpin` = $userlogged
GROUP BY `possible_friend_id`
ORDER BY COUNT( * ) DESC
LIMIT 0 , 3 
 
Thus its possible that the suggested person status in REQUEST table he/she has already a request pending to you but still being shown as suggested friend or in the BLOCKED table the suggested friend was blocked but still appear to you as suggested friend. therefore I would like to eliminate this behavior. a suggested person should not be exits either in REQUEST table or BLOCKED table. 
 
Can anyone help me solve this?
 
the following are the REQUEST table, PINME table and BLOCKED table structure
 
REQUEST TABLE:
senderpin - id of the person who send request   
friendpin - receivers request id    


BLOCKED TABLE: 
user1_id - a person who block user2_id
user2_id - the blocked person by user1_id


PINME TABLE:
PINME table: the table which keep relations
structure: 1,2 2,2 | 4,8 8,4
senderpin - id of the person who send request   
friendpin - receivers request id
 
Thanks and regards 

原文：https://stackoverflow.com/questions/17399624