Filter records that have a certain status
Try the following query (for starters):

select
  P.id
, P.name
, P.prices
, R.revision_status
, R.id
from Product P
  join Revisions R on P.revision_id = R.id
where R.revision_status &lt;&gt; '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name
    having Product.name = P.name
  ) &gt; 1
order by P.id, R.revision_status ;

--
-- result
-- "name" of the Product with the ID 4 changed to: "Produkt2"
-- before executing the query
--
+------+----------+--------+-----------------+------+
| id   | name     | prices | revision_status | id   |
+------+----------+--------+-----------------+------+
| 1    | Produkt1 | 10     | 1               | 1    |
| 3    | Produkt1 | 2      | null            | 3    |
| 4    | Produkt2 | 42     | 1               | 4    |
| 5    | Produkt2 | 43     | null            | 5    |
| 7    | Produkt3 | 1      | 1               | 7    |
| 8    | Produkt3 | 3      | null            | 8    |
| 9    | Produkt3 | 4      | null            | 9    |
+------+----------+--------+-----------------+------+
7 rows in set (0.01 sec)

-- MySQL 5.7


If this gives you the required result, maybe we should cover the (revision_status) cases '1' and 'null' explicitly, by refactoring the WHERE clause as follows:

select ... from ... -- same as above
where R.revision_status &lt;&gt; '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = '1'
  ) &gt;= 1
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = 'null'
  ) &gt;= 1
order by P.id, R.revision_status ;

-- ... = 'null' due to your test data, maybe this needs IS NULL in reality.