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.
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.
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]
