如何在mysql和php中的共同属性上联接两个表? [英] how to join two tables on common attributes in mysql and php?

问题描述

我有2张桌子:

table1

id message       user
1  testing       23
2  testing again 44
3  test..        23
5  lol           12
6  test..        6

和

table2

id user friend
1  23   44
2  23   6
3  19   12
4  23   32
5  23   76
6  23   89

我试图获取与23成为朋友的所有用户的messages，包括23

i am trying to get the messages of all users that are friends with 23 including 23

喜欢:

id message       user   id user friend
1  testing       23     n  n    n
2  testing again 44     1  23   44
3  test..        23     n  n    n
6  test..        6      2  23   6

我们可以看到12丢失了，因为他不是23的朋友，而只是19

we can see that 12 is missing because he is not friend with 23 but only with 19

我有这个

SELECT * 
FROM table1 AS w
INNER JOIN table1 AS f ON w.user = f.friend 
WHERE (w.user = 23) 

，但如果23有消息但没有朋友，它将返回null，并且还将返回23的其他朋友，例如76 and 89没有消息..

but in case 23 has messages but no friends it will return null and also this will return other friends of 23 like 76 and 89 that have no messages..

:)困惑吗?

有什么想法吗?

谢谢

推荐答案

尝试一下:

SELECT 
    table1.*, 
    table2.* 
FROM 
    table2 
        RIGHT JOIN table2 
        ON table2.friend = table1.user 
WEHRE 
    table2.user = 23

这篇关于如何在mysql和php中的共同属性上联接两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！