Select array from array mysql

0
votes

I have two table Table Category (Cate_id, Parent_id)

Cate_id | Parent_ID

1       | 0

3       | 1

2       | 0

4       | 1

And table Products (Product_id, Category_id)

Product_id | Category_id

1          | 1,3

2          | 4

How to select product ID with category_id = 1 and category have parent = 1?

1
mysqlarrays
The problem you are encountering (or going to encounter) is that Category_id within the Products table is denormalized. Normalize it. – user289086
Don't. Ever. Store. Delimited values in the relational database. – peterm
possible duplicate of What are 1NF, 2NF and 3NF in database design? – user289086

1 Answers

1
votes

If you are going to design table schema then you should use normalization to optimize.

There are no arrays in MySQL and also there is no need for it.

First off all, if a "column in a RDBMS is meant to be atomic", in that it contains one and only one piece of information. Trying to store more than one piece of data in a column is a "violation of first normal form".

So you should have to alter your logic like below,

-------------------
|Cate_id |Parent_ID|
--------------------
|1       | 0       |
--------------------
|3       | 1       |
--------------------
|2       | 0       |
--------------------
|4       | 1       |
--------------------

--------------------------
|Product_id | Category_id|
--------------------------
|1          | 1          |
--------------------------
|1          | 3          |
--------------------------
|2          | 4          |
--------------------------

Hope this query will solve your problem then.

Select prdct.Product_id
from Products as prdct, Category as ctgory
where 
  ctgory.Cate_id = prdct.Category_id
  and prdct.category_id =1
  and ctgory.Parent_ID=1;