MySQL. Need to create a table based on selected data from another table?

Question

MySQL

Good day. There is a large table with stores, products, product categories and cities where the stores are located. I need to create a table that will contain only three categories from the first table, and cities in which there are stores that fit those categories. How can I do that?

Answer 1

This is very elementary (just read any documentation):
Here, for example, create a table table1 (or create it through any GUI):

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `surname` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We fill it with test data (or fill it through any GUI):

INSERT INTO `table1` (`id`, `name`, `surname`) VALUES
  (1, 'David', 'Fisher'),
  (2, 'David', 'Lynch'),
  (3, 'David', 'Gilmour'),
  (4, 'Ian', 'Gillan');

Then, using this command, we create table2, which will contain the data from table1, where name='David'
This is the simplest example - of course, it is possible in another way, but I think you, based on this example, will be able to disassemble the technique.