Select all rows contains same value in a column-CodePudding

I want to select all package_id that contain product_id 2. In this case, package_id 1,3,5 has product_id 2

Table: product_package

package_id    package_name    product_id
---------------------------------------------
1               Gold                1,2,3
2               Platinum            4,5,12
3               Diamond             2,11,5
4               Titanium            3,5
5               Basic               2

I tried:

SELECT
                   *
                FROM
                    product_package
                    WHERE product_id IN(2)

It is outputting package_id 3 and 5 only. How do I output this properly? product_id structure is varchar(256). Should I change the structure or add Foreign keys?

CodePudding user response：

We always recommend not to stored delimited columns see Is storing a delimited list in a database column really that bad?

But you can use FIND_IN_SET but this is always slow

SELECT
                   *
                FROM
                    product_package
                    WHERE FIND_IN_SET(2,product_id)

package_id	package_name	product_id
1	Gold	1,2,3
3	Diamond	2,11,5
5	Basic	2

fiddle

CodePudding user response：

First, let me explain what is happening in your query.

You have WHERE product_id IN(2), but product_id is a misnomer and should rather be product_ids, because it is multiple IDs unfortunately stored in a string. IN is made to look up a value in a list. Your list, however, only consists of one element, so you can just as well use the equality operator: WHERE product_id = 2.

What you have is WHERE string = number, so the DBMS has to convert one of the values in order to compare the two. It converts the string to a number (so '2' matches 2 and '002' matches 2, too, as it should). But your strings are not numbers. The DBMS should raise an error on '1,2,3' for instance, because '1,2,3' is not a number. MySQL, however, has a design flaw here and still converts the string, regardless. It just takes as many characters from the left as they still represent a number. '1' does, but then the comma is not considered numerical (yes, MySQL cannot deal with a thousand separator when convertings strings to numbers implicitly). So converting '1,2,3' to a number results in 1. Equally, '2,11,5' results in 2, so rather surprisingly '2,11,5' = 2 in MySQL. This is why you are getting that row.

You ask "Should I change the structure", and the answer to this is yes. So far your table doesn't comply with the first normal form and should thus not exist in a relational database. You'll want two tables instead forming the 1:n relation:

Table: package

package_id	package_name
1	Gold
2	Platinum
3	Diamond
4	Titanium
5	Basic

Table: product_package

package_id	product_id
1	1
1	2
1	3
2	4
2	5
2	12
3	2
3	11
3	5
4	3
4	5
5	2

You ask "or add Foreign keys?", and the answer is and add foreign keys. So with the changed structure you want product_package(product_id) to reference product(product_id) and product_package(package_id) to reference package(package_id).

CodePudding user response：

Disregarding that you should not be storing multiple values in a single field, you can use LIKE operator to achieve what you are looking for. I'm going with assumptions:

all values are delimited with commas
all values are integers
there are no whitespaces (or any other characters besides integers and commas)

select * from product_package
where product_id like '2,%'
   or product_id like '%,2,%'
   or product_id like '%,2'
   or product_id like '2'

Alternatively, you can use REGEXP operator:

select * from product_package
where product_id regexp '^2$|^2,. |. ,2,. |. ,2'

References: