Find exact and partial matches in the tables
use OR instead of AND, also 'cat' is LIKE '%cat% so you do not need to check with = also 

SELECT P.*, U.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.STORE_ID)
            WHERE P.PRODUCT_NAME LIKE ('%cat%')
               OR U.USERNAME LIKE ('%cat%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID