PostgreSQL – How to Find Exact and Partial Matches in Tables

postgresql

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.

Best Answer

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

Related Solutions

PostgreSQL WHERE Clause – Prioritizing Conditions with OR

You have two conditions to check during the authentication:

There is a user with matching username; do not need to match by email.
There are no users with matching usernames; need to match by email.

So the query is:

WITH matching_username AS (SELECT * FROM member WHERE username = 'john@domain.com')
SELECT * FROM matching_username WHERE (SELECT COUNT(*) FROM matching_username) = 1
                                   AND pw_hash = 'x1j34'
UNION 
SELECT * FROM member            WHERE (SELECT COUNT(*) FROM matching_username) = 0
                                   AND email = 'john@domain.com' 
                                   AND pw_hash='x1j34';

On the other hand, there is a problem with your design of the table.

What if two users have have the same email, but neither of them have email as their username? If one of them tries to authenticate with an email, you will not be able to tell, which user record to authenticate against.

PostgreSQL Joins – How to Inner Join Some Tables and Outer Join Others

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation:

FULL OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

SELECT *
FROM   users           u  
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

This returns all categories and also returns all user-product combinations for the given user_id.

If you are enforcing referential integrity with foreign keys and products.category_id is defined NOT NULL, so that every product is assigned to an existing category (not in your question), you can replace the FULL JOIN with a RIGHT JOIN.

Plus, either way, the condition to select a specific user has to move to a subquery or (simpler) to a JOIN condition between the first three tables. It has to be applied before the last table categories is joined.

SQL Fiddle.

Best Answer

Related Solutions

PostgreSQL WHERE Clause – Prioritizing Conditions with OR

PostgreSQL Joins – How to Inner Join Some Tables and Outer Join Others

Related Question