How to add multiple AND conditions on same columns on a LEFT JOIN query

Question

I'm trying to apply multiple groups of conditions on a left join, for ex :

SELECT p.id
FROM project p 
LEFT JOIN project_data d ON p.id = d.project_id 
WHERE (d.field_id = 1 AND d.value != "") AND (d.field_id = 3 AND d.value > 100)

Here is my db structure :

project_data
+----+-----------+----------+----------+
| id | project_1 | field_id |  value   |
+----+-----------+----------+----------+
|  1 |         1 |        1 |          |
|  3 |         1 |        3 | 80       |
|  4 |         2 |        1 | SomeData |
|  6 |         2 |        3 | 105      |
|  7 |         3 |        1 | SomeData |
|  9 |         3 |        3 | 50       |
+----+-----------+----------+----------+

So what i'm trying to achieve is to return ONLY the project 2, who have a project data with field_id = 1 and a non empty value, and another project data with field_id = 3 and a value > 100.

But this returns an empty list. I guess it's because I have multiples conditions on the same columns.

This seems pretty easy, but I haven't find a way to do that.

Thank you for your help!

[EDIT]

I found a solution, but really not sure it's the best one (please let me know if you have something better)

SELECT p.id
FROM project p 
JOIN project_data d ON p.id = d.project_id AND ((d.field_id = 1 AND d.value != "") OR (d.field_id = 3 AND d.value > 100))
GROUP BY p.id
HAVING COUNT(DISTINCT d.field_id) = 2

Answer 1

Try this:

SELECT p.id
FROM project p 
LEFT JOIN project_data d 
  ON p.id = d.project_id 
  AND d.field_id IN (1,3) 
  AND d.value != "" 
  AND d.value > 100;

In a LEFT JOIN environment, the most left table is the reference table. In your case it's the project table. The purpose of a LEFT JOIN is to return whatever data from the most left table regardless of it has a match with other table(s) after it or not. If you do a WHERE condition on table after the reference, you're just breaking the LEFT JOIN and turn it into a normal join instead. Unless, you're specifically trying to find non-matching data in the table reference.

See some examples here

New detail suggests that OP is actually looking any matches from the second table; which, with the same query I suggested above, can be done by simply changing the left join to a normal join. Therefore:

SELECT *
FROM project p 
JOIN project_data d 
  ON p.id = d.project_id 
  AND d.field_id IN (1,3) 
  AND d.value != "" 
  AND d.value > 100;

Updated fiddle