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

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.

CodePudding user response：

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

CodePudding user response：

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