1

I have two tables users, products

users table

id   product_id
1    [1,2,3]
2    [5,6]
3    [4]

Products table

id   product
1     Milk
2     Bread
3     Soup
4     Noodles
6     Suger
7     Biscuits
8     Raw Eggs

How to apply joins on these tables.

Here is what i am trying to do.

select * from products join users ON find_in_set(products.id, users.product_id)

But, The output is incorrect.

Please guide me how to implement this.

Mark
  • 49
  • 6
  • 2
    You might like to read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/20860) – Bill Karwin Jul 24 '20 at 16:44

2 Answers2

1

Here's how to fix it:

CREATE TABLE user_products (
  user_id INT,
  product_id INT,
  PRIMARY KEY (user_id, product_id)
);

Fill one user and one product id into each row.

INSERT INTO user_products (user_id, product_id) 
VALUES (1,1), (1,2), (1,3), (2,5), (2,6), (3,4);

Now you can do the join this way:

SELECT * FROM users AS u
JOIN user_products AS up ON u.id = up.user_id
JOIN products AS p ON up.product_id = p.id;

Don't use JSON arrays for joins.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

This is working for me for few data from your sample instead of find_in_set i would recommend you use REGEXP_LIKE

WITH USERS AS (
SELECT 1 AS ID , '[1,2,3]' AS product_id UNION ALL 
SELECT 2, '[5,6]' UNION ALL 
SELECT 3, '[4]'
),
PRODUCTS AS (
SELECT 1 AS ID , 'Milk' AS PRODUCT UNION ALL 
SELECT 2, 'Bread' UNION ALL 
SELECT 3, 'Soup' UNION ALL
 SELECT 4, 'Noodles' 
)
 select * from PRODUCTS join USERS ON REGEXP_LIKE(USERS.PRODUCT_id,  PRODUCTS.id);

this is the query :

select * from PRODUCTS join USERS ON REGEXP_LIKE(USERS.PRODUCT_id,  PRODUCTS.id)

output im getting is

# ID, PRODUCT, ID, product_id
   1,   Milk,   1, [1,2,3]
   2,  Bread,   1, [1,2,3]
   3,   Soup,   1, [1,2,3]
   4, Noodles,  3, [4]
JagaSrik
  • 700
  • 7
  • 23