PHP SQL LEFT JOIN 多个值

Question

我一直在尝试并尝试找到解决方案，但有点卡住了。

我有一个包含 2 列的“ session ”表，其中包含来自“成员”的 ID，我需要成员的详细信息显示在我的 session 列表中。

我试过了，但是没用...

$q = "SELECT (meetings.id) AS meetings_id, meetings.columnA, meetings.ColumnB, (members.id) AS members_id, members.first, members.last FROM meetings **LEFT JOIN members ON meetings.columnA = members.id AND meetings.columnB = members.id**";
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_assoc($r)) { }

如果我只加入一个值，'columnA' 或 columnB'，我只能让它工作，但这显然只检索了单个成员的数据。

虽然这种方式可行但并不理想......

$q = "SELECT * FROM meetings";
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_assoc($r)) { 
    $qm = "SELECT * FROM members WHERE id = '$row[columnA]'";
    $rm = mysqli_query($dbc, $qm);
    while($rowm = mysqli_fetch_assoc($rm)) {
        if  ($row['columnA'] == $rowm['id']) { echo $rowm['first']; }
    }
    $qm = "SELECT * FROM members WHERE id = '$row[columnB]'";
    $rm = mysqli_query($dbc, $qm);
    while($rowm = mysqli_fetch_assoc($rm)) { 
        if  ($row['columnB'] == $rowm['id']) { echo $rowm['first']; }
    }
}

有没有办法让 JOIN 工作或更好的方法，这样我只需要进行一个查询？

谢谢。

Answer 1

我认为您只需要两个 left join。

SELECT m.id AS meetings_id, m.columnA, m.ColumnB,
       me1.id AS members_id, me1.first, me1.last,
       me2.id AS members_id, me2.first, me2.last
FROM meetings m LEFT JOIN 
     members me1
     ON m.columnA = me1.id LEFT JOIN
     members me2
     ON me.columnB = me2.id;