How Can I Efficiently Find Records with Multiple Values in a Single Database Column?

Finding Records with Multiple Values in a Column

In a relational database, it's common to store multiple values within a single column. However, when querying data with such columns, it can be challenging to isolate specific values. Let's explore a scenario where we have a table with a "children" column containing multiple names separated by commas.

Problem:

Consider the following table:

id	name	children
1	Roberto	Michael,Dia
2	Maria	John,Alex
3	Mary	Alexandre,Diana

The objective is to find the parent who has a child named Alex. While using "WHERE children = 'Alex'" would be ideal, it fails due to the multiple names in each cell. Employing "WHERE children LIKE '%Alex%'" returns additional unrelated matches.

Normalized Schema:

To address this issue, the table schema can be normalized by creating a separate table for children with one row for each child. The original table can then be joined with the child table to locate the parent with a specific child. However, if this solution is not feasible, there is an alternative:

FIND_IN_SET Function:

The MySQL FIND_IN_SET() function can be used to check if a specific value is present within a comma-delimited list. For this scenario, the query would be:

WHERE FIND_IN_SET('Alex', children)

This query will return the rows where 'Alex' is found in the 'children' column.

The above is the detailed content of How Can I Efficiently Find Records with Multiple Values in a Single Database Column?. For more information, please follow other related articles on the PHP Chinese website!