我现有的SQL查询:
$getEdu = "SELECT * FROM Request_Subject WHERE REQUEST_ID = $id
AND SUBJECT_ID IN (2,3,4)";
因此,在我的数据库中,每个REQUEST_ID
可以与多个SUBJECT_IDs
关联
并且CCD_ 3具有从CCD_。
因此,我在表中现有的值是:
REQUEST_ID: 1 -> SUBJECT_IDs: 2,3
REQUEST_ID: 2 -> SUBJECT_IDs: 2,4
REQUEST_ID: 3 -> SUBJECT_IDs: 2,8
因此,当前运行查询时,REQUEST_ID = 3
仍将包含在结果中,因为它具有SUBJECT_ID = 2
。
有没有可能的方法让我创建一个SQL查询,即使有一个值匹配,查询也会忽略REQUEST_ID
,因为它与数组的值不同。
提前谢谢。
更新
当前结果:
$requestSubjects = array();
// So if I call REQUEST_ID = 3
$getEdu = "SELECT * FROM Request_Subject WHERE REQUEST_ID = 3
AND SUBJECT_ID IN (2,3,4)";
$getEdu_answer = mysqli_query($connection, $getEdu);
if(!$getEdu_answer || mysqli_num_rows($getEdu_answer)==0) {
echo "Error";
die();
}
else {
while($subjectRow = mysqli_fetch_assoc($getEdu_answer)) {
$subject = $subjectRow["Subject_ID"];
array_push($requestSubjects, $subject);
}
$reqSub = '{"reqSubject":' .json_encode($requestSubjects). '}';
echo $reqSub; // Returning a JSON to ajax
}
回声结果:
{"reqSubject":[2]}
结果是正确的,因为REQUEST_ID=3
与SUBJECT_ID = 2
相关联。
然而,我想要的是,由于REQUEST_ID=3
也与SUBJECT_ID = 8
相关联,所以它根本不应该响应结果。
如果我理解正确,那么REQUEST_ID的所有SUBJECT_ID都要匹配(2,3,4),然后才能打印结果?如果是,则可以为每个SUBJECT_ID 构造查询
编辑:
- 您必须使用另一个sqlquery获取REQUEST_ID的ALLSUBJECT_ID
- 联系$getEdu查询的SUBJECT_ID,然后再执行$get爱德华查询
示例:
$getEdu = "SELECT * FROM Request_Subject WHERE REQUEST_ID = $id";
//$sub_list is the all subect_id of $id
foreach ($sub_list as $sub_id){
$getEdu.=" AND $sub_id IN (2,3,4)";
}
$getEdu_answer = mysqli_query($connection, $getEdu);
如果您在示例REQUEST_ID=3上执行它,$qetEdu查询应为:
SELECT * FROM Request_Subject WHERE REQUEST_ID = 3 AND 2 IN (2,3,4) AND 8 IN (2,3,4)
意味着REQUEST_ID=3不会返回,因为8不在(2,3,4)中
希望能有所帮助。
可能是
$getEdu = "SELECT * FROM Request_Subject WHERE REQUEST_ID=3
AND SUBJECT_ID IN (2,3,4, if(REQUEST_ID=3,8,null))";
如果我理解正确的话。。。