Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select all rows contains same value in a column

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?

Tags:

3

Answers


  1. 0

    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:

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search