我无法正确编写正确的SQL查询

Question

我在数据库中有 2 个表，并且 ı 正在尝试根据某些条件过滤一些用户。

criterias table
+---------+----------------------+--------------------+----------------------+
| user_id | searching_friendship | searching_practice | conversation_subject |
+---------+----------------------+--------------------+----------------------+
| 31      |                      |                    | science              |
| 26      |  on                  | on                 | love                 |
| 32      |  on                  | off                | science              |
| 34      |                      |                    |                      |
+---------+----------------------+--------------------+----------------------+

user table
+---------+------------+-----------------------------+---------------+-------------+---------------+--------------+------------------+
| user_id | user_name  | user_email                  | user_password | user_gender | user_language | language_lvl | last_login       |
+---------+------------+-----------------------------+---------------+-------------+---------------+--------------+------------------+
| 26      | Furkan     | [e-mail] | 123           |             | Türkiye      | basic        | 16.06.2019 11:57 |
| 31      | sfsdf      | [e-mail]        | 123           | Male        | Afrikaans     | basic        | 09.06.2019 20:01 |
| 32      | denemeuser | [e-mail]             | 123           | Male        | Amharic       | intermediate | 16.06.2019 11:57 |
| 33      | Smith      | ssdf                        | 123           | male        | ing           | upper        | NULL             |
| 34      | luser      | [e-mail]             | 123456        | Male        | Afrikaans     | basic        | 16.06.2019 10:32 |
+---------+------------+-----------------------------+---------------+-------------+---------------+--------------+------------------+

我正在尝试匹配具有相同条件的用户。我所做的是

$userId=$_SESSION['userId'];
$sql="SELECT* FROM criterias WHERE user_id='$userId'";
$query=mysqli_query($conn,$sql);
while($result=mysqli_fetch_assoc($query)){
    $friendshipCheck=$result['searching_friendship'];
    $pracCheck=$result['searching_practice'];
    $conversationSub=$result['conversation_subject'];
}

所以我正在获取当前的标准信息，并且正在尝试与这样的其他用户匹配

SELECT* FROM users,criterias WHERE (users.user_id=criterias.user_id AND users.user_id!='$userId') AND criterias.searching_friendship='$friendshipCheck' OR criterias.searching_practice='$pracCheck'

但它不起作用。你知道如何解决这个亲爱的朋友吗？

Answer 1

如果您只想获取与您的条件相同的用户，则此查询应该有效

SELECT *
FROM users AS u
INNER JOIN criterias AS c ON u.user_id = c.user_id
WHERE u.user_id != '$userId'
  AND c.searching_friendship = '$friendshipCheck'
  AND c.searching_practice = '$pracCheck'
  AND c.conversation_subject = '$conversation_subject'

Answer 2

提供预期的结果与你得到的结果将有助于证明最佳解决方案。在这里，我尝试根据对您尝试在代码中编写的内容的理解来编写查询：

;WITH  CTE AS
    (
           SELECT  C.*
           FROM    Criterias C
           WHERE   C.user_id = <user_id_input>
    )
    SELECT  *
    FROM    User U
            INNER JOIN Criterias C
                ON U.user_id = C.user_id
    WHERE   EXISTS ( 
                       SELECT 1
                       FROM   CTE 
                       WHERE  CTE.searching_friendship = C.searching_friendship
                       OR     CTE.searching_practice   = C.searching_practice
                   )
     AND        U.user_id != <input_user_id> ;