0

I am trying to select the most relevant products from a table that match the currently viewed product based on the number of matching product categories.

Sample table A

-------------------------------------
| ID | Name | Desc | Price | Active |
-------------------------------------
| 1  | Car  | NR   | $1.00 | Y      |
-------------------------------------
| 2  | Phone| NR   | $2.00 | Y      |
-------------------------------------
| 3  | Pen  | NR   | $1.00 | Y      |
-------------------------------------
| 4  | Book | NR   | $2.00 | Y      |
-------------------------------------
| 5  | Light| NR   | $3.00 | Y      |
-------------------------------------
| 6  | Rock | NR   | $1.00 | Y      |
-------------------------------------
| 7  | Plum | NR   | $1.00 | N      |
-------------------------------------

Sample table B

---------------
| ID | Cat_ID |
---------------
| 1  | 2      |
---------------
| 1  | 3      |
---------------
| 2  | 1      |
---------------
| 2  | 2      |
---------------
| 2  | 5      |
---------------
| 3  | 3      |
---------------
| 4  | 2      |
---------------
| 4  | 3      |
---------------
| 4  | 6      |
---------------
| 5  | 3      |
---------------
| 6  | 2      |
---------------
| 6  | 3      |
---------------
| 6  | 4      |
---------------
| 7  | 2      |
---------------
| 7  | 3      |
---------------
| 7  | 4      |
---------------

When looking at product ID 1 I want to run a query to show the viewer products matching ID's 4 and 6 as other possible options since they share the most category_ID's with number 1 and they are active (Y). In reality, the products might share anywhere from 1 to 30 different categories. It is fine if there are 40 matches, I will use limit 2 to get only two of them.

The issue I am having is that I cannot figure out how to match both the active column from table A and the cat_ID column from Table B against the array of resulting ID's from the Table B query.

The result I want to receive from Table A:

--------------------------------
| 4  | Book | NR   | $2.00 | Y |
--------------------------------
| 6  | Rock | NR   | $1.00 | Y |
--------------------------------

1 Answer 1

0

as variant:

SET @ID = 1;
SELECT
    ID,
    count(*) as count_matched
FROM TableB
WHERE 
    ID != @ID
    AND
    CAT_ID IN (SELECT CAT_ID FROM TableB WHERE ID = @ID )
GROUP BY ID
ORDER BY count(*) DESC
LIMIT 2;

add after comment:

MySQL do not support LIMIT in subqueries, so You can:

  • Handle returned values in application and use it for prepared statements as parameters
  • Use temporary tables
SET @ID = 1;
DROP TEMPORARY TABLE IF EXISTS tt;
CREATE TEMPORARY TABLE tt as
SELECT
    ID,
    count(*) as count_matched
FROM TableB
WHERE 
    ID != @ID
    AND
    CAT_ID IN (SELECT CAT_ID FROM TableB WHERE ID = @ID )
GROUP BY ID
ORDER BY count(*) DESC
LIMIT 2;
SELECT    
    TableA.* FROM TableA WHERE     ID IN (SELECT ID FROM tt);

temporary table is session, so You could use it with same name independent for each session.

But if handle returned values in application - prevent table locks, because CREATE TABLE AS SELECT (and INSERT SELECT) - require lock table, with external code - it just 2 independent selects.

3
  • Thank you! But I mis-worded my question. I have updated it with what I wanted as a result (the matching rows from Table A). That is the part I cannot figure out. How to get the matching rows from multiple results of the first part.
    – V_RocKs
    May 1, 2018 at 5:03
  • Why would they need LIMIT in a siubquery for this problem? May 1, 2018 at 9:12
  • @ypercubeᵀᴹ - it normal case, when thousands (and many - more then 50% could use some categories ) products could use the same categories. In my vision it just more proper way - reduce number of rows on inner query, rather than - return all, than sort, but sure - this is not only one way
    – a_vlad
    May 1, 2018 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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