sql连接查询问题

Question

我做了一个朋友列表，连接2个表格，FRIENDS表格和PLAYERS表格。

朋友表

player target  status 
----------------------------- 
john  eric  invited 
roger moore  friends 
stan  winston friends 

球员表

name  pic  
------------------- 
john  lol.jpg 
stan  skinhead.gif 

现在的球员页面上的时候，我需要遍历friends表，并找出他们有几个朋友。所以为此我必须检查两个player字段，以及target字段，即= $ name

然后获取图片ID必须加入players表。

这是迄今为止我的查询，它不工作明显（伪位）：

SELECT p.pic, p.name FROM friends f INNER JOIN players p ON (player OR target)=p.name WHERE (f.player='" .$name. "' OR f.target='" .$name. "') AND status='friends' 

$ name是玩家的设定档名称上。

任何人都可以告诉我如何调整，使其工作？

Answer 1

您需要使用联合才能在一个查询中获取全部内容。

SELECT * FROM friends JOIN players ON (target = name AND status = friends) 
WHERE player = ? 
UNION ALL 
SELECT * FROM friends JOIN players ON (player = name AND status = friends) 
WHERE target = ? 

Answer 2

SELECT p.pic, p.name FROM friends f INNER JOIN players p ON f.name=p.name WHERE (f.player='" .$name. "' OR f.target='" .$name. "') AND status='friends“

朋友= f.player 在查询？

Answer 3

试试这个：

SELECT p.pic, 
     p.name 
FROM friends f 
    INNER JOIN players p ON p.name IN (f.player, f.target) 
WHERE (f.player='" .$name. "' OR f.target='" .$name. "') 
AND status='friends' 

这是

SELECT p.pic, 
     p.name 
FROM friends f 
    INNER JOIN players p ON p.name = f.player OR p.name = f.target 
WHERE (f.player='" .$name. "' OR f.target='" .$name. "') 
AND status='friends'