我想从两个表中获取数据。从第一个表中，我需要的所有名称不是登录用户的名称，但登录用户的名称必须在这两列中的一列中。我想用这个结果从第二个表中得到第一个表中给出的每个名称的照片的名称。

table 1 names
+++++++++++++++++++++++++
id | name1   | name2    |
+++++++++++++++++++++++++
1  | john    | lea      |<- i need lea because john is in one of those two columns
-------------------------
2  | peter   | john     |<- i need peter because john is in one of those two columns
-------------------------
3  | mark    | paola    |<- no john here so query should ignore
__________________________

table 2 users
+++++++++++++++++++++++++
id | name    | photo    |
+++++++++++++++++++++++++
1  | lea     | la.jpg   |<- I want to use lea given with SELECT IF to get name of photo
-------------------------
2  | peter   | pt.jpg   |<- I want to use peter given with SELECT IF to get name of photo
-------------------------
2  | mark    | mk.jpg   |<- no match from SELECT IF so query should ignore
-------------------------

我的select if或concat工作得很好，但是当我尝试将其与内部连接一起使用时，根本没有结果。我的代码：

$username = 'john';
$sql = "SELECT IF( name1 = '$username', name2, name1 ) AS otheruser
FROM names
WHERE name1 = '$username' OR name2 = '$username'";

上面的代码工作正常。现在我尝试在查询中添加另一个具有内部联接的表。很明显，代码的内部连接部分并没有得到其他用户的结果，所以输出是“无结果”。
我的尝试是这样的：

$sql = "SELECT IF(names.name1 = '$username', names.name2, names.name1) AS otheruser, users.photo
FROM names
INNER JOIN users ON users.name = 'otheruser'
WHERE names.name1 = '$username' OR names.name2 = '$username'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {
$names = $row['otheruser'];
$photos = $row['photo'];
}
} else {echo "no results";}