How can I filter one column by two AND conditions?

The question has been resolved. But if you have a “better” or another way to do it then feel free to add a comment! Thanks all for reading! 🙂

I’m trying to make a dynamic query. Everything is working perfectly except for one thing. I’ve Google’d for days but I can’t figure out how I can make the following work;

SELECT project.name, project.description, track.name, track.description
   , SDG.position, SDG.title, SDG.description
   , sprint_numbers.number, sprint_options.option
   , resources.name, resources.description
   , URLs.URL 
FROM project INNER JOIN track ON project.track_id = track.id 
INNER JOIN project_SDG ON project.id = project_SDG.project_id 
INNER JOIN SDG ON project_SDG.SDG_id = SDG.id
INNER JOIN sprint ON sprint.project_id = project.id 
INNER JOIN sprint_numbers ON sprint_numbers.id = sprint.sprint_number_id 
INNER JOIN sprint_options ON sprint_options.id = sprint.sprint_option_id 
INNER JOIN resources ON project.id = resources.project_id 
INNER JOIN URLs ON URLs.id = resources.id 
WHERE 1=1 
   AND MATCH (project.name) AGAINST (:name_project) 
   AND MATCH (project.description) AGAINST (:description_project) 
   AND SDG.id = :SDG_1 
   AND SDG.id = :SDG_2

The query executes but does not return anything. The problem is that the SDG.id can’t be true to both :SDG_1 and :SDG_2.

Using the OR operator works, but that does not return it the way I want. It must “act” as an AND operator. (:SDG_1 & :SDG_2 are the names of the PHP variables that bind to the SQL statement parameters.)

The query should filter for both values. The values given to :SDG_1 and :SDG_2 must both exist in the SDG.id column of the project_SDG table. If the value of :SDG_1 exists, but :SDG_2 not, then the query should not return anything.

I found this on StackOverflow but it did not work for me: SELECTING with multiple WHERE conditions on same column

I hope someone can help me out.

EDIT: minimal reproducible example

QUERY:

SELECT * FROM project
INNER JOIN project_SDG ON project.id = project_SDG.project_id 
INNER JOIN SDG ON project_SDG.SDG_id = SDG.id
WHERE SDG.id = 1 AND SDG.id = 7 AND SDG.id = 14 AND SDG.id = 17

Project table

+------------------+---------------------------+------------+
|     id  name     |        description        |  track_id  |
+------------------+---------------------------+------------+
| 1   project name | This is a description   2 |            |
+------------------+---------------------------+------------+

SDG table

+-----+-----------+-------------+---------------------------------------------+
| id  | position  |   title     |                 description                 |
+-----+-----------+-------------+---------------------------------------------+
|   1 |         1 | SDG 1 to 17 | There're multiple SDGs ranging from 1 to 17 |
|  17 |        17 | SDG 1 to 17 | There're multiple SDGs ranging from 1 to 17 |
+-----+-----------+-------------+---------------------------------------------+

project.SDG (bridge-table)

+------------+--------+
| project.id | SDG.id |
+------------+--------+
|          1 |      1 |
|          1 |      2 |
|          1 |      3 |
+------------+--------+

Answer

You want for each project.id both values :SDG_1 and :SDG_2 to exist for SDG.id, so use this in the WHERE clause:

WHERE 1=1 
    AND MATCH (project.name) AGAINST (:name_project) 
    AND MATCH (project.description) AGAINST (:description_project) 
    AND project.id IN (
        SELECT project_id
        FROM project_SDG
        WHERE SDG_id IN (:SDG_1, :SDG_2)
        GROUP BY project_id
        HAVING COUNT(DISTINCT SDG_id) = 2
    )

Advertisement

Answer