MySQL: Compare comma separated values with values in different rows

Question

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

Mihai · Accepted Answer · 2016-04-18 10:32:11Z

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

edited Apr 18, 2016 at 10:32

answered Apr 18, 2016 at 10:30

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mitkosoft Over a year ago

maybe SEPARATOR ', ' shall be used because of white space between values?

Anoop Kumar Over a year ago

Sequence of geographies in T2 is not fixed, it can be G2, G1 or G1, G3, G2. So I don't think ORDER BY in group_concat will work for me.

Mihai Over a year ago

@AnoopVerma Do you have some sort of order common to both tables,like a primary key?

Anoop Kumar Over a year ago

No there is nothing common in both the tables. T1 is real table in DB while in T2 I am getting the data from user, so if T1 already have data for any record in T2 than I need to inform user that this record is already present. I am thinking to define order in T2, than your query will work. thank you.

Mihai Over a year ago

@AnoopVerma The only way is to break the concated values and reorder them

1000111 · Accepted Answer · 2016-04-18 10:59:13Z

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

Collectives™ on Stack Overflow

MySQL: Compare comma separated values with values in different rows

Table T1

Table T2

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table T1

Table T2

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related