从具有 2 个条件和 3 个表的表中检索信息

Question

<?phpdefine('DB_HOST', 'localhost');define('DB_USER', 'root');define('DB_PASS', '');define('DB_NAME', 'websitefinal');//connecting to database and getting the connection object$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);//Checking if any error occured while connectingif (mysqli_connect_errno()) {    echo "Failed to connect to MySQL: " . mysqli_connect_error();    die();} else {}if ($_SERVER['REQUEST_METHOD'] == 'GET') {    $id = $_POST['user_id'] ?? '';    $query = "SELECT UL.* FROM users UL INNER JOIN active_crew L JOIN search SL      ON L.user_id = UL.user_id AND L.search_id = SL.search_id";    $sql = mysqli_query($conn, $query);    $result2 = array();    while ($row = mysqli_fetch_array($sql)) {        array_push($result2, array(            'user_id' => $row[0],            'name_full' => $row[1],            'Propic' => $row[4]));    }    echo json_encode($result2);    mysqli_close($conn);}我正在尝试检索在特定搜索“搜索表”中值班的活跃船员表中的用户“用户表”的信息，但我没有收到任何信息我想从“user_id”主键和外键检索数据的用户表crew 表只有 2 列 search_id 和 user_id 都是外键应具有职责描述“search_id”主键和外键的搜索表键关系对所有列都有效 search_id.crew with search_id.search user_id.crew with user_id.user

Answer 1

ON您必须在each 的子句中设置每个连接的条件JOIN，而不是在 last 中设置所有连接的条件ON：

SELECT UL.* 

FROM users UL 

INNER JOIN active_crew L ON L.user_id = UL.user_id 

INNER JOIN search SL ON L.search_id = SL.search_id