Does MySql store multiple links for another table inside the same cell and select it?

Question

I have two tables

table: people id name goods_owned 1 john 1,4,3 2 Mike 2,5 3 Sam 1,5,2 4 Andy 5,3,4 

-

 table goods: g_id g_name g_class 1 sugar food 2 salt food 3 boat transp 4 house habitation 5 car transp 

this is a simple example of the goods table, in fact it is very long, and each person in people can have several goods assigned to him, for example. car, boat, sugar, etc. there are no restrictions on how much a person can have and is completely random. I could not find a better way to save it than shared by a coma, for example. 1,5,3

I have a problem with the choice I need, for example.

 SELECT people.*, goods.name FROM people LEFT JOIN goods ON goods.g_id = people.goods_owned WHERE name = "Sam" 

However, the problem is that the product has several product identifiers in the cell, and they must be broken down somehow to get the answer:

 1, Sam, sugar, car, salt 

If you know a better alternative to storing (for example, 100) multiple values ​​coma separated in one cell, then please let me know.

Answer 1

A person can be associated with zero, one or more goods. Good can be associated with zero, one or more people.

This is a many-to-many relationship.

We usually handle this by creating a third table, which is a “relation” that points to two other tables

 table: goods_owned people_id goods_id 1 1 1 4 1 3 2 2 2 5 3 1 3 5 3 2 4 5 4 3 4 4 

The combination of these two columns can be specified as unique and can serve as the main key for the table. Each column can be defined as a foreign key for parent tables.

 CREATE TABLE goods_owned ( people_id INT UNSIGNED NOT NULL , goods_id INT UNSIGNED NOT NULL , PRIMARY KEY (people_id, goods_id) , KEY FK_goods_owned_people (people_id) , KEY FK_goods_owned_goods (goods_id) , CONSTRAINT FK_goods_owned_people FOREIGN KEY (people_id) REFERENCES people (id) , CONSTRAINT FK_goods_owned_goods FOREIGN KEY (goods_id) REFERENCES goods (g_id) ) ; 

Another alternative is if the set of “products” is static and clearly defined and should not be presented in a table, then you can use the MySQL SET data type and have one table. But this approach is only suitable if the set is static (it does not need to be changed.)

Answer 2

Your database is not normalized. You have repeating groups inside columns . You should normalize your database, if at all possible.

If you cannot change the design of the database, you can use FIND_IN_SET , but it will be sloooooooow:

 SELECT people.id, people.name, people.goods_owned, goods.name FROM people LEFT JOIN goods ON FIND_IN_SET(goods.g_id, people.goods_owned) WHERE name = 'Sam' 

Answer 3

Let me just be bored ...

 CREATE TABLE `people` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; CREATE TABLE `goods` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; CREATE TABLE `goods_owned` ( `people_id` INT(11) DEFAULT NULL, `goods_id` INT(11) DEFAULT NULL ) ENGINE=MYISAM CHARSET=latin1 ; CREATE TABLE `classes` ( `id` int(11) NOT NULL, `class_name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; INSERT INTO `people` (`id`, `name`) VALUES('1','John'); INSERT INTO `people` (`id`, `name`) VALUES('2','Mike'); INSERT INTO `people` (`id`, `name`) VALUES('3','Sam'); INSERT INTO `people` (`id`, `name`) VALUES('4','Andy'); INSERT INTO `classes` (`id`, `class_name`) VALUES('1','Food'); INSERT INTO `classes` (`id`, `class_name`) VALUES('2','Trans'); INSERT INTO `classes` (`id`, `class_name`) VALUES('3','Habitation'); INSERT INTO `goods` (`id`, `name`, `class_id`) VALUES('1','Sugar','1'); INSERT INTO `goods` (`id`, `name`, `class_id`) VALUES('2','Salt','1'); INSERT INTO `goods` (`id`, `name`, `class_id`) VALUES('3','Boat','2'); INSERT INTO `goods` (`id`, `name`, `class_id`) VALUES('4','House','3'); INSERT INTO `goods` (`id`, `name`, `class_id`) VALUES('5','Car','2'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('1','1'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('1','4'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('1','3'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('2','2'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('2','5'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('3','1'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('3','5'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('3','2'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('4','5'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('4','3'); INSERT INTO `goods_owned` (`people_id`, `goods_id`) VALUES('4','4'); SELECT people.name , goods.name , classes.class_name FROM people LEFT JOIN goods_owned ON (people.id = goods_owned.people_id) LEFT JOIN goods ON (goods_owned.goods_id = goods.id) LEFT JOIN classes ON (goods.class_id = classes.id) WHERE classes.id = 1 /*Include only Food Goods*/ ORDER BY people.name; ;