php - 如何获取除特定值之外的所有行

Question

您好，有没有办法只获取组织尚未使用的survey_name？

`survey table`
+-----------+-------------+
| survey_id | survey_name |
+-----------+-------------+
|         1 | name1       |
|         2 | name2       |
|         3 | name3       |
|         4 | name4       |
|         5 | name5       |
+-----------+-------------+

`link table`
+---------+-------------+-----------------+
| link_id | survey_link | organisation_id |
+---------+-------------+-----------------+
|       1 | 1(survey_id)|        1        |
|       2 | 2           |        1        |
|       3 | 2           |        2        |
|       3 | 3           |        2        |
|       3 | 6           |        2        |
+---------+-------------+-----------------+

在此数据库结构中，您将看到每个组织的情况:

可用的调查组织1:

• 姓名3
• 姓名4
• 姓名5

可用的调查组织1:

• 姓名1
• 姓名4
• 姓名5

我尝试过使用哪里不是 (survey_id= $row['survey_id'])

//get all survey's that are in use
$sqlCheckLink = "SELECT `survey_id` FROM `link_info` WHERE `organisation_id`=".$_SESSION['organisation_id']."";
    $resultCheck = mysqli_query($conn, $sqlCheckLink);
    if ($resultCheck ->num_rows > 0) {
        while ($id = $resultCheck -> fetch_assoc()) {
            //show all surveys that are available
            $sqlGetSurveys = "SELECT * FROM `survey_info` WHERE NOT (survey_id = ".$id['survey_id'].")";
            $resultAllSurveys = mysqli_query($conn, $sqlGetSurveys);

            if ($resultAllSurveys ->num_rows > 0) {
                while ($row = $resultAllSurveys-> fetch_assoc()) {
                   //echo content
                }
            }
        }
    }

来自 here但它似乎不起作用......通过这种方法，我还可以获得正在使用的调查。

如果有人可以提供帮助，我们将不胜感激!

Answer 1

仅使用左连接:

SELECT t.survey_name
FROM survey_table as t
LEFT JOIN link_table AS l ON l.survey_link = t.survey_id
WHERE l.link_id IS NULL

未经测试，但你可以明白这个想法。我希望这对您有帮助。