How can I filter one column by two AND conditions?

Question

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 1

Could you provide a minimal reproducible example for your query?
Generally speaking, one field cannot be equal to two different values in the same time. So, you have either mixed up the logical operators or you need two different fields.

I can assume that in your case there may be several related records with different values. In this case, you need to join the same table twice with different aliases. Let's say as SDG1 and SDG2. After that you can compare

... `SDG1`.id = :SDG_1 AND `SDG2`.id = :SDG_2

Update:

The win trick is groupping. You can enumerate all required SDG IDs and count how many of them is in group. Just for example in case of two IDs:

SELECT project.id
FROM project
JOIN project_SDG ON project_SDG.project_id = project.id
JOIN SDG ON SDG.id = project_SDG.SDG_id
WHERE SDG.id IN(1,2)
GROUP BY project.id
HAVING COUNT(*) = 2

See my sandbox here: https://www.db-fiddle.com/f/pixe3Zcs75Mq2PyCYPk913/0

If you need all project's fields, you have to put this into sub-query as

... WHERE id IN ( subquery here )

Subquery example: https://www.db-fiddle.com/f/pixe3Zcs75Mq2PyCYPk913/1

Answer 2

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
    ) 

Answer 3

I have already answered here, but I have another approch.

1. Find bunch of IDs assotiated with some project

To find project IDs we can test lonely pivot table without any join:

  SELECT project_id FROM project_SDG
  WHERE SDG_id IN(1,2,6)
  GROUP BY project_id HAVING COUNT(*) = 3

it gives us list of Project IDs

2. Access all project fields and add extra conditions

SELECT project.*
FROM project
JOIN (
  SELECT project_id FROM project_SDG
  WHERE SDG_id IN(1,2,6)
  GROUP BY project_id HAVING COUNT(*) = 3
) AS ids ON ids.project_id = project.id
WHERE
  MATCH(project.name) AGAINST ('project') AND
  MATCH(project.description) AGAINST ('sit')

you can play with it here: https://www.db-fiddle.com/f/pixe3Zcs75Mq2PyCYPk913/3

3. Prepare query on the PHP side

I will use known technique to prepare SQL statement.

$ids = [1, 2, 6]; // it can come from request parameters
$text1 = 'project';
$text2 = 'sit';
// build ?,?,?,... pattern
$qmarks = implode(',', array_fill(0, count($ids), '?'));
// Use SQL query above
$sth = $dbh->prepare("
SELECT project.*
FROM project
JOIN (
  SELECT project_id FROM project_SDG
  WHERE SDG_id IN({$qmarks})
  GROUP BY project_id HAVING COUNT(*) = ?
) AS ids ON ids.project_id = project.id
WHERE
  MATCH(project.name) AGAINST (?) AND
  MATCH(project.description) AGAINST (?)
");
$sth->execute(array_merge($ids, [count($ids), $text1, $text2]));
$records = $sth->fetchAll();