Select array from array mysql

Question

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?

The problem you are encountering (or going to encounter) is that Category_id within the Products table is denormalized. Normalize it. — user289086
– user289086, Commented Jan 3, 2015 at 7:46
Don't. Ever. Store. Delimited values in the relational database. — peterm
– peterm, Commented Jan 3, 2015 at 18:40
possible duplicate of What are 1NF, 2NF and 3NF in database design? — user289086
– user289086, Commented Jan 3, 2015 at 19:58

Sridhar DD · Accepted Answer · 2015-01-03 20:32:37Z

1

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;

edited Jan 3, 2015 at 20:32

user289086

answered Jan 3, 2015 at 4:59

Sridhar DD

1,9801 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gia Minh Nim Over a year ago

But the category_id is array, it will be (1,3,4)

Sridhar DD Over a year ago

There is no arrays in MySQL since there is no need.

Collectives™ on Stack Overflow

Select array from array mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related