在一个查询中加入/查看Mysql中的三个表

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.

目的是在数据库表中检查可能根据我朋友的朋友知道的朋友(如果user1 = user2和user2 = user4 posibile user1知道user4,user4是建议)我使用以下代码来检查这个并且是按计划工作。

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.

因此,REQUEST表中的建议人员状态可能已经向您提出待处理请求,但仍然显示为建议的朋友,或者在BLOCKED表中,建议的朋友被阻止但仍然显示给您作为建议的朋友。因此我想消除这种行为。建议的人不应该在REQUEST表或BLOCKED表中退出。

Can anyone help me solve this?

谁能帮我解决这个问题?

the following are the REQUEST table, PINME table and BLOCKED table structure

以下是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

Thanks and regards

感谢致敬

1 个解决方案

#1

What you seem to need to do is join the pinme table against itself, and then against itself again to find the matches. Do a LEFT OUTER JOIN with the results of that against the blocked table and against the request table to exclude any records where there is already a blocked or request record.

你似乎需要做的是将pinme表连接到自身,然后再次反对自己找到匹配。对阻塞表和请求表执行LEFT OUTER JOIN以及结果,以排除已存在阻塞或请求记录的任何记录。

Something like this:-

像这样: -

SELECT DISTINCT c.aPerson
FROM (SELECT friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend FROM pinme WHERE friendpin = $userlogged) a
INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) b
ON a.aFriend = b.aFriend
INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) c
ON b.aPerson = c.aFriend
LEFT OUTER JOIN (SELECT user1_id AS blocked FROM blocked WHERE user2_id = $userlogged UNION SELECT user2_id AS aFriend FROM blocked WHERE user1_id = $userlogged) d
ON c.aPerson = d.blocked
LEFT OUTER JOIN (SELECT senderpin AS blocked FROM request WHERE friendpin = $userlogged UNION SELECT friendpin AS aFriend FROM request WHERE senderpin = $userlogged) e
ON c.aPerson = e.blocked
WHERE d.blocked IS NULL
AND  e.blocked IS NULL
AND c.aPerson != $userlogged

EDIT - Modified suggestion

编辑 - 修改建议

SELECT DISTINCT c.aPerson
FROM (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme WHERE friendpin = $userlogged) a -- Find friends
INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) b -- Find friends of friends who are not the original person
ON a.aFriend = b.aFriend AND a.aPerson != b.aFriend
LEFT OUTER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme WHERE friendpin = $userlogged) c -- Find friends
ON c.aFriend = b.aPerson 
LEFT OUTER JOIN (SELECT user1_id AS blocked FROM blocked WHERE user2_id = $userlogged UNION SELECT user2_id AS aFriend FROM blocked WHERE user1_id = $userlogged) d
ON b.aPerson = d.blocked
LEFT OUTER JOIN (SELECT senderpin AS blocked FROM request WHERE friendpin = $userlogged UNION SELECT friendpin AS aFriend FROM request WHERE senderpin = $userlogged) e
ON b.aPerson = e.blocked
WHERE d.blocked IS NULL
AND  e.blocked IS NULL
AND  c.aFriend IS NULL
AND b.aPerson != $userlogged

#1