CODEWITHSUNDEEP
phpsqlmysqldatabase
mTuran
mTuran

Reputation: 1834

Search SQL Question Between Related Two Tables

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:

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

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.

Upvotes: 2

Views: 173

Answers (2)

Cade Roux
Cade Roux

Reputation: 89671

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.

Upvotes: 3

VoteyDisciple
VoteyDisciple

Reputation: 37803

It sounds like you're looking for an EXISTS query, which verifies that matching rows exist in a table, but without performing a 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;

Upvotes: 3

Related Questions