CODEWITHSUNDEEP
mysql
Anoop Kumar
Anoop Kumar

Reputation: 488

MySQL: Compare comma separated values with values in different rows

I have following requirements.

I have two tables T1 and T2 like

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:

Product     Geography
P2          G1, G2

Upvotes: 1

Views: 188

Answers (2)

1000111
1000111

Reputation: 13519

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

SQL FIDDLE DEMO

TEST (If you cannot access 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

Upvotes: 0

Mihai
Mihai

Reputation: 26804

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

Upvotes: 1

Related Questions