CODEWITHSUNDEEP
mysqljoin
Subway
Subway

Reputation: 5716

MySQL: Comparing records within the same table to each other

In a database that represents a store, I have the following table:

table name: 
  CLIENTS_PRODUCTS
columns:
  client_id (INT)
  product_name (CHAR(256))

As you can see, each product purchase is stored as a record in the table.

Given a client A, I want to find all clients X where there is any product of A (pA) and any product of X (pX) such that pA is a prefix of pX.

To make it short: I need to execute a comparison between different records within the same table. I thought doing it by JOINing CLIENTS_PRODUCTS on itself. Is this the right way?

I searched around in SO and couldn't find a direct answer to this one.

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

This version assumes that product_names contains a single product (despite the name of the column):

select distinct cp.client_id
from (select 
      from clients_products cp
      where client_id = A
     ) a join
     client_products cp
     on cp.product_names like concat(a.product_names, '%') and
        cp.client_id <> a.client_id

If product_names is really a comma delimited list of products, then we can modify this as:

select distinct cp.client_id
from (select 
      from clients_products cp
      where client_id = A
     ) a join
     client_products cp
     on concat(',', cp.product_names, ',') like concat('%,', a.product_names, '%,%') and
        cp.client_id <> a.client_id

Upvotes: 1

Erin Schoonover
Erin Schoonover

Reputation: 539

The structure doesn't seem conducive to what you're trying to accomplish, but I suppose you could hack it to work using a REGEXP expression. This may be slow, depending on the size of the table.

SELECT DISTINCT prod_parents.client_id 
FROM CLIENTS_PRODUCTS AS products
JOIN CLIENTS_PRODUCTS AS prod_parents ON 
  prod_parents.product_names REGEXP CONCAT("^",products.product_names)
  #AND prod_parents.client_id <> products.client_id
WHERE products.client_id = ? AND products.product_names = ?

Uncomment the AND on the JOIN if you don't want client A to be returned with the rest.

An alternative would be to add a parent_id to the table, and if a new row is being inserted that matches a product_names prefix, it would be assigned that record's id as parent_id. Your application would handle regexing the product prefix, so that load would be taken off the database. You'd be left with a simple integer comparison via modifying the above SP's JOIN ... ON products.id=prod_parents.parent_id

Upvotes: 0

Related Questions