-1

My problem is: the products.cats field is like this: ["12"] or sometimes ["15", "12"]

However, the value of categories.id is always int. So Always 12 or 15

I want it to find one of the categories in the products.cats section (it doesn't matter) with an id value of 12 or 15. And I want to shoot with JOIN.

And I want to pull the ones whose category values are published.

But I can't do it with JOIN because products.cats value ["12"] has such a value.How can I map, check and join these 2 tables?

My Query:

SELECT *
FROM products
LEFT JOIN categories ON categories.id = products.cats
WHERE products.status='published'
AND categories.status='published'
ORDER BY products.created_at DESC
LIMIT 0,100

How can I sync them?

source.category joined.category

Can
  • 64
  • 12
  • That's a very short MySQL query.. what does it do? – FanoFN Mar 25 '21 at 06:05
  • This is actually part of my sorghum. I can only say that I had problems in the JOIN and pairing part. I did not add it because there was no problem in other parts. I'm pulling data from the database and I'm stuck in this part. @FaNo_FN – Can Mar 25 '21 at 06:09
  • 1
    Post data as text not image. `["joined.category"] = source.category` is not a query. I have no idea what are asking, either. Are you trying to joint those two columns? – Allan Wind Mar 25 '21 at 06:12
  • So does that means you have two tables, one is `joined` and the other is `source`? And both of the table have a `category` column in which one of them is `varchar` and the other `int` datatype? And the value of `source.category` is wrapped within bracket+double quote while `joined.category` is just `integer`? And now you're trying to join and match these two columns but since `["2"] <> 2` you're trying to figure out how to match them? – FanoFN Mar 25 '21 at 06:20
  • Yes, that's exactly my problem. And I updated the content again to explain it better. I think it's got better now. I'm sorry I didn't use too much SO. @FaNo_FN – Can Mar 25 '21 at 06:25
  • For testing purposes provide textual CREATE TABLE for both tables, sample data as INSERT INTO (3-5 rows per table enough), desired output for this data. Also specify precise MySQL version. – Akina Mar 25 '21 at 06:29

1 Answers1

-1
.. ON source.category->>"$[0]" = joined.cat_id ..
Akina
  • 39,301
  • 5
  • 14
  • 25