MySQL compare two lists to say does they exists in each other

Question

I am writing app, and I have three tables (places, categoriesandmain_categories`).

Main categories are array of list of categories ids (like main category "Sport" can have "Football stadiums" and "pools"). I am trying to ask the backend for places from main category. I have problem, because places can also have multiple categories, so I have records like this :

Main categories table :

| main_category | categories |
| ------------- | ---------- |
|     sport     |   1,3,7,8  |

places table :

|     place_name   | categories |
| ---------------- | ---------- |
| xxx team statium |   1,8,11   |

How can I display in MySQL places with in main_category list?

For example above sport should show all places with categories 1,3,7 and 8.

IS your categories column a text column? Please edit and show you table Explainer. — Martin, Dec 07 '21 at 15:48
[Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462) **Well yes!** — RiggsFolly, Dec 07 '21 at 15:50

score 0 · Answer 1 · answered Dec 07 '21 at 16:11

You need to join both tables in order to find out the corresponding place_name for each main_categories categories column, the problem here is that categories is a text list.

You really should consider using a n:n relationship with a pivot table and a specific table for category. Something like maincategories_category for the main_categories table and another for the places table like places_category.

So you'll end up with 5 tables:

category:

| category      |
| 1             |
| 3             |
| 7             |
| 8             |
| 11            |

main_categories table :

| main_category |
|     sport     |

main_categories_category pivot table:

| id | main_category | category |
| 1  | sport         | 1        |
| 2  | sport         | 3        |
| 3  | sport         | 7        |
| 1  | sport         | 8        |

places table :

|     place_name   |
| ---------------- |
| xxx team statium |

places_category pivot table:

| id | place_name       | category |
| 1  | xxx team statium | 1        |
| 1  | xxx team statium | 8        |
| 1  | xxx team statium | 11       |

Then you can play with queries. I.e:

select place_name from places_category 
where category IN 
  (
   select category from main_categories_category 
   where main_category = 'sport'
  )

score 0 · Answer 2 · answered Dec 07 '21 at 16:28

If you table sport looks like this:

CREATE TABLE sport (id int, name varchar(20));
INSERT INTO TABLE sport VALUES
   (1,'Footbal'),
   (3,'Snooker'),
   (7,'Cricket'),
   (8,'AnotherSport'),
   (9,'this one is not mentioned');

then you could do:

SELECT 
   id, name

FROM sport
INNER JOIN categories ON 
     INSTR( CONCAT(',',categories,','), CONCAT(',',sport.id,','))>0 AND
       main_category='sport';

see DBFIDDLE

MySQL compare two lists to say does they exists in each other

2 Answers2