I have following requirements.

我有以下要求。

I have two tables T1 and T2 like

我有两张T1和T2表

Table T1

Product     Geography
P1          G1
P2          G1
P2          G2

Table T2

Product     Geography
P1          G1, G2
P2          G1, G2

I want a query to get data from table T2 if comma separated Geography have exactly matching records in T1. If there are less or more geographies in any table than it should not return that row. Sequence of geographies in T2 is not fixed. So return of that query from above example will be:

如果逗号分隔的Geography在T1中具有完全匹配的记录,我想要查询从表T2获取数据。如果任何表中的地理位置越来越少,则不应返回该行。 T2中的地理顺序不固定。因此,从上面的示例返回该查询将是:

Product     Geography
P2          G1, G2

2 个解决方案

#1

Join on concating values

加入联合值

SELECT t2.Product,t2.geography FROM t2
JOIN 
(SELECT  t1.Product,GROUP_CONCAT(t1.geography ORDER BY t1.Geography SEPARATOR ', ') as concatgeo FROM t1
GROUP BY t1.product)x
ON t2.Geography=x.concatgeo AND t2.Product=x.Product

#2

SELECT 
firstTable.Product,
firstTable.geographies
FROM
(
   SELECT 
    Product,
    REPLACE(GROUP_CONCAT(Geography),' ','') AS geographies
   FROM T1
   GROUP BY Product) firstTable

INNER JOIN 

(
   SELECT 
    Product,
    REPLACE(Geography,' ','') AS geographies
   FROM T2 ) secondTable

ON firstTable.Product = secondTable.Product
WHERE firstTable.geographies = secondTable.geographies;

Note: I've replaced the spaces using REPLACE function just to ensure the two queries from two tables generate the same string

注意:我已经使用REPLACE函数替换了空格,以确保来自两个表的两个查询生成相同的字符串

SQL FIDDLE DEMO

SQL FIDDLE DEMO

TEST (If you cannot access sqlfiddle):

测试(如果你不能访问sqlfiddle):

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Product` varchar(50) CHARACTER SET utf8 NOT NULL,
  `Geography` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`ID`)
);
INSERT INTO `t1` VALUES ('1', 'P1', 'G1');
INSERT INTO `t1` VALUES ('2', 'P2', 'G1');
INSERT INTO `t1` VALUES ('3', 'P2', 'G2');

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Product` varchar(50) CHARACTER SET utf8 NOT NULL,
  `Geography` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`ID`)
);
INSERT INTO `t2` VALUES ('1', 'P1', 'G1, G2');
INSERT INTO `t2` VALUES ('2', 'P2', 'G1, G2');

Running the above query on this test data you will get output like below:

对此测试数据运行上述查询,您将获得如下输出:

Product geographies
P2         G1,G2

#1