All geek questions in one place

Join multiple MySQL tables

I. Introduction

I create a support system in which a user from a certain country raises a problem in a certain category, and experts from that country, administrative unit and category receive this problem.

Ex A user from Germany with zip code 1000 is causing a problem in the Software category. Experts from Germany and / or the provinces with boundaries of the zip code MIN_PROVINCE_ZIPCODE <= 1000 >= MAX_PROVINCE_ZIPCODE and / or the region with borders of the zip code MIN_REGION_ZIPCODE <= 1000 >= MAX_REGION_ZIPCODE , and the Software categories get this problem.

i.e.: Select all problems in which the country of issue is equal to the country of the expert, and the category of issue is equal to one of the categories of experts, and / or the postal code of the issue is greater than or equal to the minimum register register and less than or equal to the maximum register register of the province and / or zip The code is greater than or equal to the minimum size of the region’s zip code, and the release code is less than or equal to zip codes.

"and / or" means that if specialists are assigned to accept problems from a specific administrative unit (s), if they are not indicated, then they are assigned everything that corresponds to their country and category

II. Database schemas and records

* Keep in mind! *

a) Experts may be part of ...

  • one, several or without category (s)
  • one, several or no province
  • one, several or none of the region (s)

b) If the experts are NOT part ...

  • category, then no problems will be assigned to them
  • province, then all questions for their country and category will be assigned to them
  • region, then all problems for their province (s) will be assigned to them

1. Schemas

 CREATE TABLE IF NOT EXISTS `categories` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(300) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `provinces` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `country` varchar(300) NOT NULL, `province` varchar(300) NOT NULL, `min_zipcode` int(5) unsigned NOT NULL, `max_zipcode` int(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `regions` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `provinceid` int(11) unsigned NOT NULL, `region` varchar(300) NOT NULL, `min_zipcode` int(5) unsigned NOT NULL, `max_zipcode` int(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `issues` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `categoryid` int(11) unsigned NOT NULL, `country` varchar(150) NOT NULL, `zipcode` int(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `experts` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `country` varchar(150) NOT NULL DEFAULT 'none', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `experts_categories` ( `expertid` int(11) unsigned NOT NULL, `categoryid` int(11) unsigned NOT NULL, PRIMARY KEY (`expertid`,`categoryid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `experts_provinces` ( `expertid` int(11) unsigned NOT NULL, `provinceid` int(11) unsigned NOT NULL, PRIMARY KEY (`expertid`,`provinceid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `experts_regions` ( `expertid` int(11) NOT NULL, `regionid` int(11) NOT NULL, PRIMARY KEY (`expertid`,`regionid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. Entries

 INSERT INTO `categories` (`id`, `name`) VALUES (1, 'Software'), (2, 'Hardware'); INSERT INTO `experts` (`id`, `country`) VALUES (1, 'Germany'), (2, 'France'), (3, 'Germany'); INSERT INTO `experts_categories` (`expertid`, `categoryid`) VALUES (1, 1), (1, 2), (2, 1), (3, 1); INSERT INTO `experts_provinces` (`expertid`, `provinceid`) VALUES (1, 4), (2, 6), (2, 7); INSERT INTO `experts_regions` (`expertid`, `regionid`) VALUES (1, 8), (1, 10); INSERT INTO `issues` (`id`, `categoryid`, `country`, `zipcode`) VALUES (1, 2, 'Germany', 2100), (2, 1, 'France', 1900), (3, 1, 'Germany', 1500), (4, 2, 'Germany', 2800), (5, 2, 'France', 1850); INSERT INTO `provinces` (`id`, `country`, `province`, `min_zipcode`, `max_zipcode`) VALUES (1, 'Germany', 'Province One', 1000, 1299), (2, 'Germany', 'Province Two', 1300, 1499), (3, 'Germany', 'Province Three', 1500, 1999), (4, 'Germany', 'Province Four', 2000, 2899), (5, 'France', 'Province One', 1000, 1799), (6, 'France', 'Province Two', 1800, 2199), (7, 'France', 'Province Three', 2200, 2399); INSERT INTO `regions` (`id`, `provinceid`, `region`, `min_zipcode`, `max_zipcode`) VALUES (1, 1, 'Region One', 1000, 1099), (2, 1, 'Region Two', 1100, 1159), (3, 1, 'Region Three', 1160, 1299), (4, 2, 'Region One', 1300, 1400), (5, 2, 'Region Two', 1401, 1499), (6, 3, 'Region One', 1500, 1699), (7, 3, 'Region Two', 1700, 1999), (8, 4, 'Region One', 2000, 2299), (9, 4, 'Region Two', 2300, 2599), (10, 4, 'Region Three', 2600, 2699), (11, 4, 'Region Four', 2700, 2899), (12, 5, 'Region One', 1000, 1699), (13, 5, 'Region Two', 1700, 1799), (14, 6, 'Region One', 1800, 2000), (15, 6, 'Region Two', 2001, 2199), (16, 7, 'Region One', 2200, 2299), (17, 7, 'Region Two', 2300, 2399);

3. Visual schemes

 mysql> DESC `categories`; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(300) | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ mysql> DESC `provinces`; +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+----------------+ | id | bigint(11) unsigned | NO | PRI | NULL | auto_increment | | country | varchar(300) | NO | | NULL | | | province | varchar(300) | NO | | NULL | | | min_zipcode | int(5) unsigned | NO | | NULL | | | max_zipcode | int(5) unsigned | NO | | NULL | | +-------------+---------------------+------+-----+---------+----------------+ mysql> DESC `regions`; +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | provinceid | int(11) unsigned | NO | | NULL | | | region | varchar(300) | NO | | NULL | | | min_zipcode | int(5) unsigned | NO | | NULL | | | max_zipcode | int(5) unsigned | NO | | NULL | | +-------------+------------------+------+-----+---------+----------------+ mysql> DESC `issues`; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | id | bigint(11) unsigned | NO | PRI | NULL | auto_increment | | categoryid | int(11) unsigned | NO | | NULL | | | country | varchar(150) | NO | | NULL | | | zipcode | int(5) | NO | | NULL | | +------------+---------------------+------+-----+---------+----------------+ mysql> DESC `experts`; +---------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | country | varchar(150) | NO | | none | | +---------+------------------+------+-----+---------+----------------+ mysql> DESC `experts_categories`; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | expertid | int(11) unsigned | NO | PRI | NULL | | | categoryid | int(11) unsigned | NO | PRI | NULL | | +------------+------------------+------+-----+---------+-------+ mysql> DESC `experts_provinces`; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | expertid | int(11) unsigned | NO | PRI | NULL | | | provinceid | int(11) unsigned | NO | PRI | NULL | | +------------+------------------+------+-----+---------+-------+ mysql> DESC `experts_regions`; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | expertid | int(11) | NO | PRI | NULL | | | regionid | int(11) | NO | PRI | NULL | | +----------+---------+------+-----+---------+-------+

4. Visual recordings

 mysql> SELECT * FROM `categories`; +----+----------+ | id | name | +----+----------+ | 1 | Software | | 2 | Hardware | +----+----------+ mysql> SELECT * FROM `provinces`; +----+---------+----------------+-------------+-------------+ | id | country | province | min_zipcode | max_zipcode | +----+---------+----------------+-------------+-------------+ | 1 | Germany | Province One | 1000 | 1299 | | 2 | Germany | Province Two | 1300 | 1499 | | 3 | Germany | Province Three | 1500 | 1999 | | 4 | Germany | Province Four | 2000 | 2899 | | 5 | France | Province One | 1000 | 1799 | | 6 | France | Province Two | 1800 | 2199 | | 7 | France | Province Three | 2200 | 2399 | +----+---------+----------------+-------------+-------------+ mysql> SELECT * FROM `regions`; +----+------------+--------------+-------------+-------------+ | id | provinceid | region | min_zipcode | max_zipcode | +----+------------+--------------+-------------+-------------+ | 1 | 1 | Region One | 1000 | 1099 | | 2 | 1 | Region Two | 1100 | 1159 | | 3 | 1 | Region Three | 1160 | 1299 | | 4 | 2 | Region One | 1300 | 1400 | | 5 | 2 | Region Two | 1401 | 1499 | | 6 | 3 | Region One | 1500 | 1699 | | 7 | 3 | Region Two | 1700 | 1999 | | 8 | 4 | Region One | 2000 | 2299 | | 9 | 4 | Region Two | 2300 | 2599 | | 10 | 4 | Region Three | 2600 | 2699 | | 11 | 4 | Region Four | 2700 | 2899 | | 12 | 5 | Region One | 1000 | 1699 | | 13 | 5 | Region Two | 1700 | 1799 | | 14 | 6 | Region One | 1800 | 2000 | | 15 | 6 | Region Two | 2001 | 2199 | | 16 | 7 | Region One | 2200 | 2299 | | 17 | 7 | Region Two | 2300 | 2399 | +----+------------+--------------+-------------+-------------+ mysql> SELECT * FROM `issues`; +----+------------+---------+---------+ | id | categoryid | country | zipcode | +----+------------+---------+---------+ | 1 | 2 | Germany | 2100 | | 2 | 1 | France | 1900 | | 3 | 1 | Germany | 1500 | | 4 | 2 | Germany | 2800 | | 5 | 2 | France | 1850 | +----+------------+---------+---------+ mysql> SELECT * FROM `experts`; +----+---------+ | id | country | +----+---------+ | 1 | Germany | | 2 | France | | 3 | Germany | +----+---------+ mysql> SELECT * FROM `experts_categories`; +----------+------------+ | expertid | categoryid | +----------+------------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 3 | 1 | +----------+------------+ mysql> SELECT * FROM `experts_provinces`; +----------+------------+ | expertid | provinceid | +----------+------------+ | 1 | 4 | | 2 | 6 | | 2 | 7 | +----------+------------+ mysql> SELECT * FROM `experts_regions`; +----------+----------+ | expertid | regionid | +----------+----------+ | 1 | 8 | | 1 | 10 | +----------+----------+

III. Decision

I managed to find half the solution.

1. My half decision

a) Request:

 SELECT `i`.`id` `issue_id`, `e`.`id` `expert_id` FROM `issues` `i` INNER JOIN `experts` `e` ON `i`.`country` = `e`.`country` INNER JOIN `experts_categories` `ec` ON `e`.`id` = `ec`.`expertid` AND `i`.`categoryid` = `ec`.`categoryid` ORDER BY `e`.`id`, `ec`.`categoryid` ASC

b) Result:

 +----------+-----------+ | issue_id | expert_id | +----------+-----------+ | 3 | 1 | | 1 | 1 | | 4 | 1 | | 2 | 2 | | 3 | 3 | +----------+-----------+

c) Exact result:

 +----------+-----------+ | issue_id | expert_id | +----------+-----------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----------+-----------+

An explanation of why the above visual result is accurate.

First, let's get a “full connection” so that we can make comparisons:
d) Request:

 SELECT `i`.`id` `issue_id`, `e`.`id` `expert_id`, `i`.`categoryid` `issue_category_id`, `ec`.`categoryid` `expert_category_id`, `i`.`country` `issue_country`, `e`.`country` `expert_country`, `i`.`zipcode` `issue_zipcode`, `p`.`id` `province_id`, `p`.`min_zipcode` `province_min_zipcode`, `p`.`max_zipcode` `province_max_zipcode`, `r`.`id` `region_id`, `r`.`min_zipcode` `region_min_zipcode`, `r`.`max_zipcode` `region_max_zipcode` FROM `issues` `i` INNER JOIN `experts` `e` ON `i`.`country` = `e`.`country` INNER JOIN `experts_categories` `ec` ON `ec`.`expertid` = `e`.`id` AND `i`.`categoryid` = `ec`.`categoryid` LEFT JOIN `experts_provinces` `ep` ON `e`.`id` = `ep`.`expertid` LEFT JOIN `provinces` `p` ON `ep`.`provinceid` = `p`.`id` LEFT JOIN `experts_regions` `er` ON `e`.`id` = `er`.`expertid` LEFT JOIN `regions` `r` ON `er`.`regionid` = `r`.`id` AND `p`.`id` = `r`.`provinceid` ORDER BY `e`.`id`,`ec`.`categoryid` ASC

e) Result:

 +----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+ | issue_id | expert_id | issue_category_id | expert_category_id | issue_country | expert_country | issue_zipcode | province_id | province_min_zipcode | province_max_zipcode | region_id | region_min_zipcode | region_max_zipcode | +----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+ | 3 | 1 | 1 | 1 | Germany | Germany | 1500 | 4 | 2000 | 2899 | 10 | 2600 | 2699 | | 3 | 1 | 1 | 1 | Germany | Germany | 1500 | 4 | 2000 | 2899 | 8 | 2000 | 2299 | | 1 | 1 | 2 | 2 | Germany | Germany | 2100 | 4 | 2000 | 2899 | 10 | 2600 | 2699 | | 1 | 1 | 2 | 2 | Germany | Germany | 2100 | 4 | 2000 | 2899 | 8 | 2000 | 2299 | | 4 | 1 | 2 | 2 | Germany | Germany | 2800 | 4 | 2000 | 2899 | 10 | 2600 | 2699 | | 4 | 1 | 2 | 2 | Germany | Germany | 2800 | 4 | 2000 | 2899 | 8 | 2000 | 2299 | | 2 | 2 | 1 | 1 | France | France | 1900 | 7 | 2200 | 2399 | NULL | NULL | NULL | | 2 | 2 | 1 | 1 | France | France | 1900 | 6 | 1800 | 2199 | NULL | NULL | NULL | | 3 | 3 | 1 | 1 | Germany | Germany | 1500 | NULL | NULL | NULL | NULL | NULL | NULL | +----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+

So, comparing (b) the query result with (c), a manually fixed result, we can notice ...

  • issue_id number 3 may NOT be assigned expert_id number 1 , because issue_id number 1 is from Germany , like an expert, assigned to category_id number 2 , like an expert, BUT has a zip code of 1500 , and expert_id number 1 assigned to accept issues only of the province_id number 4 and regions_id number 8 and 10 within the province . Therefore, the regions zip codes range from 2000 to 2299 and from 2600 to 2699 , where our issue zip code does not belong.
  • issue_id number 1 can be assigned expert_id number 1 , because issue_id number 1 - the country of Germany , like an expert, is assigned to category_id number 2 , like an expert, has a zip code 2100 , which is between the boundaries of province_id number 4 and region_id number 8 within the province, which is assigned expert_id number 1 .
  • issue_id number 4 may NOT be assigned expert_id number 1 , because issue_id number 4 is from the country of Germany , like an expert, it is assigned to category_id number 4 , BUT has a zip code 2800 , which is within the province_id number 4 , but it is not within the boundaries of the region_id number 8 and 10 , to which expert_id number 1 is assigned
  • issue_id number 2 can be assigned expert_id number 2 , since issue_id number 2 is in the country of France , like the expert, category_id 1 assigned, as with the expert, it has a zip code 1900 , which is located inside the boundaries of province_id number 6 , assigned to coverage by this expert.
  • issue_id number 3 can be assigned expert_id number 3 , since issue_id number 3 is in Germany , like an expert, category_id 1 assigned, just like an expert. In addition, this expert has no restrictions on administrative separation. That is, this expert can take issues from all Germany

Thus, we have indicated all issues that experts can be assigned.

2. Lack of half the decision

As you can see, my decision half does not take into account the limitations of administrative division.
I cannot use procedures or views to achieve this, however, I can split it into multiple queries if that helps.

The database is MySQL (5.0.1 - MySQL Community Server (GPL)), and the programming language is PHP (5.1).

+6
source share
2 answers

I am just modifying the answer from @krubo.

If you need a sub-query, the query will look like this:

 SELECT tis.id AS issue_id, tex.id AS expert_id, tis.categoryid AS issue_category_id, tex.categoryid AS expert_category_id, tis.country AS issue_country, tex.country AS expert_country, tis.zipcode AS issue_zipcode, tis.provinceid AS province_id, tis.province_minzipcode AS province_minzipcode, tis.province_maxzipcode AS province_maxzipcode, tis.regionid AS region_id, tis.region_minzipcode AS region_minzipcode, tis.region_maxzipcode AS region_maxzipcode FROM ( SELECT i.id, categoryid, i.country, zipcode, provinces.id AS provinceid, provinces.min_zipcode AS province_minzipcode, provinces.max_zipcode AS province_maxzipcode, regions.id AS regionid, regions.min_zipcode AS region_minzipcode, regions.max_zipcode AS region_maxzipcode FROM issues AS i LEFT JOIN provinces ON i.country=provinces.country AND i.zipcode BETWEEN provinces.min_zipcode AND provinces.max_zipcode LEFT JOIN regions on provinces.id=regions.provinceid AND i.zipcode BETWEEN regions.min_zipcode AND regions.max_zipcode ) AS tis JOIN ( SELECT e.id, country, categoryid, provinceid, regionid FROM experts e JOIN experts_categories ON e.id=experts_categories.expertid LEFT JOIN experts_provinces ON e.id=experts_provinces.expertid LEFT JOIN experts_regions ON e.id=experts_regions.expertid ) AS tex WHERE tis.country=tex.country AND tis.categoryid=tex.categoryid AND (tis.provinceid IS NULL OR tex.provinceid IS NULL OR tis.provinceid=tex.provinceid) AND (tis.regionid IS NULL OR tex.regionid IS NULL OR tis.regionid=tex.regionid);

Result:

 +----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+ | issue_id | expert_id | issue_category_id | expert_category_id | issue_country | expert_country | issue_zipcode | province_id | province_min_zipcode | province_max_zipcode | region_id | region_min_zipcode | region_max_zipcode | +----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+ | 1 | 1 | 2 | 2 | Germany | Germany | 2100 | 4 | 2000 | 2899 | 8 | 2000 | 2299 | | 2 | 2 | 1 | 1 | France | France | 1900 | 6 | 2000 | 2199 | 14 | 1800 | 2000 | | 3 | 3 | 1 | 1 | Germany | Germany | 1500 | 3 | 2000 | 1999 | 6 | 1500 | 1699 |
+3
source

Your SQL will be very complicated if you do not use at least one view to organize it. Try this view to map each issue to a region and region:

 create view viewissues as select issues.id, categoryid, issues.country, zipcode, provinces.id as provinceid, regions.id as regionid from issues left join provinces on issues.country=provinces.country and issues.zipcode between provinces.min_zipcode and provinces.max_zipcode left join regions on provinces.id=regions.provinceid and issues.zipcode between regions.min_zipcode and regions.max_zipcode;

and this view, to list the experts according to their categories, provinces, if any, and regions, if any:

 create view viewexperts as select experts.id, country, categoryid, provinceid, regionid from experts join experts_categories on experts.id=experts_categories.expertid left join experts_provinces on experts.id=experts_provinces.expertid left join experts_regions on experts.id=experts_regions.expertid;

Now your last request can be more manageable by choosing based on these views:

 select distinct viewissues.id, viewexperts.id from viewissues join viewexperts where viewissues.country=viewexperts.country and viewissues.categoryid=viewexperts.categoryid and (viewissues.provinceid is null or viewexperts.provinceid is null or viewissues.provinceid=viewexperts.provinceid) and (viewissues.regionid is null or viewexperts.regionid is null or viewissues.regionid=viewexperts.regionid);
+2
source

Source: https://habr.com/ru/post/890297/

More articles: