0

I have two tables products and users. I need to find exact and partial matches in these tables.

For example, there is a user with the username cat, and there is a product with the name cat. And there are products with the names of a good cat, red cat.

I want to get exact matches and partial matches. That is, get a user and three products.

I tried to do so, but the query returns an empty body.

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

How do I get a user and products for the word cat? Partial and exact matches.

  • 1
    Are you sure about: ON (U.USER_ID = P.STORE_ID) ? – Lennart Jul 6 at 9:41
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.