搜索相关两个表之间的SQL问题

I am writing some kind of search engine for my web application and i have a problem. I have 2 tables first of is projects table:

我正在为我的Web应用程序编写某种搜索引擎，我遇到了问题。我有2个表首先是项目表：

PROJECTS TABLE

项目表

id                  int(11)         NO      PRI     NULL    auto_increment
employer_id         int(11)         NO      MUL     NULL     
project_title       varchar(100)    NO      MUL     NULL     
project_description text            NO              NULL     
project_budget      int(11)         NO              NULL     
project_allowedtime int(11)         NO              NULL     
project_deadline    datetime        NO              NULL     
total_bids          int(11)         NO              NULL     
average_bid         int(11)         NO              NULL     
created             datetime        NO      MUL     NULL     
active              tinyint(1)      NO      MUL     NULL     

PROJECTS_SKILLS TABLE

PROJECTS_SKILLS表

project_id          int(11)         NO      MUL     NULL     
skill_id            int(11)         NO      MUL     NULL     

For example: I want ask this query to database:

例如：我想问这个查询到数据库：

1-) Skills are 5 and 7.
2-) Order results by created
3-) project title contains "php" word.
4-) Returned rows should contain projects.* columuns.
5-) Projects should be distinct(i don't want same projects in return of query).

Please write sql query that ensure these conditions. Thank You.

请编写确保这些条件的sql查询。谢谢。

2 个解决方案

#1

SELECT projects.*
FROM projects
WHERE EXISTS (
        SELECT *
        FROM projects_skills
        WHERE skill_id = 5 
            AND project_id = projects.project_id
    )
    AND EXISTS (
        SELECT *
        FROM projects_skills
        WHERE skill_id = 7
            AND project_id = projects.project_id
    )
    AND project_title LIKE '%php%'
ORDER BY created

or

要么

SELECT projects.*
FROM projects
WHERE EXISTS (
        SELECT *
        FROM projects_skills
        WHERE skill_id IN (5, 7) 
            AND project_id = projects.project_id
    )
    AND project_title LIKE '%php%'
ORDER BY created

Depending on what your intended result is.

取决于您的预期结果。

#2

It sounds like you're looking for an EXISTS query, which verifies that matching rows exist in a table, but without performing a JOIN.

听起来你正在寻找一个EXISTS查询，它验证表中是否存在匹配的行，但是没有执行JOIN。

SELECT *
FROM projects
WHERE EXISTS (SELECT 1 FROM projects_skills AS ps WHERE ps.project_id = projects.project_id AND ps.skill_id IN (5, 7))
    AND project_title LIKE '%php%'
ORDER BY created;