SQL联接两个不同的表

Question

表格中有一些列：friends:  id | id1 | id2 | accepted | notificationusers:    id | username | email | password朋友中的id1始终是朋友请求的发送者，而id2是他的朋友。$sql = "SELECT * FROM friends WHERE id1='$id' AND accepted=1 AND notification=1;";$result = mysqli_query($conn, $sql);$data = mysqli_fetch_all($result, MYSQLI_ASSOC);header("Content-Type: application/json");echo json_encode($data);$ id是从会话中获取的。我想从id（在用户中）= id2（如果是朋友）的用户获取用户名$ sql会是什么样子？

Answer 1

使用此查询可获得所需的结果。

$ sql =“从f的朋友中选择f.id，f.id1，f.id2，f.accepted，f.notification，u.id，u.username，u.email和u.password .id = f.id2其中f.accepted ='1'AND f.notification ='1'“

Answer 2

不确定您要在逻辑上实现什么。这是什么意思：id（在用户中）= id2（如果是朋友）？你能用语言描述吗

也许像这样的东西可能会给如何去做一个洞察力：

SELECT 

   sender.id1 as senderID,

   u.username as senderUsername,

   receiver.id1 as receiverID,

   u.username as receiverUsername

FROM users u 

INNER JOIN friends sender ON u.id = sender.id1

INNER JOIN friends receiver ON u.id = receiver.id2

WHERE sender.id1='$id' 

  AND receiver.accepted=1

  AND receiver.notification=1;