Based on the result from the table, get the result from another table
You need to move the condition on the username into the JOIN condition. Applying a WHERE condition on the outer joined table turns your outer join back into an inner join. 

Additionally - given your sample data - %use1r% won't match any row, I assume you meant to write %user1%. As you query only returns columns from the products table, I think you meant to use p.user_id in the group by as ell to remove duplicate products that stem from the join. 

SELECT p.*
FROM products AS p
  LEFT JOIN users AS U ON u.user_id = p.user_id
                      AND LOWER(u.username) LIKE LOWER('%use1r%') --&lt;&lt; HERE
GROUP BY p.product_id, p.user_id; 


But given your expected output, it seems you want an inner join as you only want products assigned to the matching user.

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
                 AND LOWER(u.username) LIKE LOWER('%use1r%') --&lt;&lt; HERE
GROUP BY p.product_id, p.user_id; 


With an inner join it doesn't matter where you put the condition on the username. The above is equivalent to:

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
WHERE LOWER(u.username) LIKE LOWER('%use1r%') --&lt;&lt; HERE
GROUP BY p.product_id, p.user_id; 




But given your usage of the group by I wonder if you are trying to remove duplicate products from the result. In that case an EXISTS query is maybe what you really want

SELECT p.*
FROM products AS p
WHERE EXISTS (SELECT * 
              FROM users u
              WHERE u.user_id = p.user_id
                AND LOWER(u.username) LIKE LOWER('%use1r%'));


Then you don't need to remove unwanted duplicates with a group by