我有一个查询
$cartable = DB::table('requests')
->select('requests.*','crequest.*')
->leftJoin('crequest','crequest.id','=','requests.request_id')
->whereRaw("FIND_IN_SET('$userid', crequest.user_allows)")
->orderBy('forms_cartable.id','desc')->get();
我的桌子:
requests:
+------+-------------+-------------------+-------------------+
| id | request_name | request_priority | request_status |
+------+-------------+-------------------+-------------------+
| 1 | test1 | low | process |
| 2 | test2 | low | process |
| 3 | test3 | low | process |
+------+--------------+------------------+-------------------+
crequest
+------+-------------+----------------+--------------+
| id | request_id | users_allow | c_status |
+------+-------------+----------------+--------------+
| 3 | 1 | 12,13,15 | done |
| 4 | 1 | 12 | done |
| 5 | 1 | 13 | end |
| 6 | 2 | 42,12,35 | done |
| 7 | 2 | 47,65 | done |
| 8 | 3 | 42 | open |
+------+-------------+----------------+--------------+
我想在crequest.user_allows返回请求中是否存在现有的当前用户ID(Logged),而没有crequest详细信息
看起来像这样:
(如果当前用户ID为12)请求表结果(执行查询后)
+------+-------------+-------------------+-------------------+
| id | request_name | request_priority | request_status |
+------+-------------+-------------------+-------------------+
| 1 | test1 | low | process |
| 2 | test2 | low | process |
+------+--------------+------------------+-------------------+
但是查询结果表crequest中的所有记录匹配我只需要请求行,如果crequest表中有匹配项
我不知道,php
但是您可以通过许多方式在MySql中实现它。这是其中的三种方式。
方法1:
SELECT DISINCT R.*
FROM requests R
INNER JOIN crequest CR ON R.id = CR.request_id
WHERE FIND_IN_SET ('12', CR.users_allow);
方法2:
SELECT *
FROM requests R
WHERE EXISTS (SELECT 1 FROM crequest CR WHERE R.id = CR.request_id
AND FIND_IN_SET ('12', CR.users_allow));
方法3:
SELECT *
FROM requests R
WHERE R.id in (SELECT request_id FROM crequest WHERE FIND_IN_SET ('12', CR.users_allow))
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句