Mysql INNER JOIN sub table with comma separated values from main table column

Question

I want to join the last inserted comma separated values from daily_discounts.discount_product table limit 1 in the products table. Its possible to achieve this in one query ?

Tried :

SELECT * FROM `daily_discounts` INNER JOIN products WHERE FIND_IN_SET(products.product_id , daily_discounts.discount_products)

SELECT * FROM `daily_discounts` INNER JOIN products ON FIND_IN_SET(products.product_id , daily_discounts.discount_products) LIMIT 1

Table daily_discounts:

+-------------+---------------------+
| discount_id | discount_products   |
+-------------+---------------------+
| 1           | 960,310,165,702     |
+-------------+---------------------+
| 2           | 231,822,379,420     |
+-------------+---------------------+
| 3           | 518,56,803,858      |
+-------------+---------------------+

Table products result wanted :

+------------+--------------------+
| product_id | product_sale_price |
+------------+--------------------+
| 518        | 10.25              |
+------------+--------------------+
| 56         | 11.24              |
+------------+--------------------+
| 803        | 5.55               |
+------------+--------------------+
| 858        | 13.52              |
+------------+--------------------+

I'm using two queries in order to work First I select the last inserted record on daily_discounts table:

select `discount_products` from `daily_discounts` order by `discount_id` desc limit 1

Second I Select the Products table

select `product_id`, `product_sale_price from `products` where `product_id` in (518, 56, 803, 858) order by `product_id` desc)

Fix your data model! Don't store numbers as strings! Don't store multiple values in a string! Declare foreign key relationships! — Gordon Linoff, Jul 15 '20 at 14:05
https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — spencer7593, Jul 15 '20 at 14:16

score 1 · Accepted Answer · answered Jul 15 '20 at 14:09

1

You can try using row_number()

select * from
(
SELECT *,row_number() over(order by discount_id desc) as rn
FROM `daily_discounts` 
)A
INNER JOIN products WHERE FIND_IN_SET(products.product_id,A.discount_products) and rn=1

answered Jul 15 '20 at 14:09

Fahmi

37,315
5
22
31

1

Amazing, that Works :) Thank you, – rocket_moon Jul 15 '20 at 14:14

score 1 · Answer 2 · answered Jul 15 '20 at 14:30

if "latest" row is the one with the highest value value of discount_id, assuming discount_id is a unique identifier (or primary key)

SELECT p.product_id
     , p.product_sale_price
  FROM ( -- inline view to get identifier of latest row 
         SELECT MAX(l.discount_id) AS latest_discount_id 
           FROM `daily_discounts` l
       ) m
  JOIN `daily_discounts` d
    ON d.discount_id = m.latest_discount_id
  JOIN `products` p
    ON FIND_IN_SET(p.id,d.discount_products)
 ORDER
    BY FIND_IN_SET(p.id,d.discount_products)

if there's some other column or expresssion we need to order the rows by, in order to determine which row(s) qualify and which of those is the latest, we could modify the inline view query

SELECT p.product_id
     , p.product_sale_price
  FROM ( -- inline view to get identifier of latest row 
         SELECT l.discount_id AS latest_discount_id 
           FROM `daily_discounts` l
          WHERE ... 
          ORDER BY ...
          LIMIT 1
       ) m
  JOIN `daily_discounts` d
    ON d.discount_id = m.latest_discount_id
  JOIN `products` p
    ON FIND_IN_SET(p.id,d.discount_products)
 ORDER
    BY FIND_IN_SET(p.id,d.discount_products)

Mysql INNER JOIN sub table with comma separated values from main table column

2 Answers2