Search code examples
mysqlsqljoinquery-optimizationmariadb

mysql join two table, and get field name of the matched record which has nonzero value

I got two tables like below

mysql> show tables;
+-------------------+
| Tables_in_testdbs |
+-------------------+
| dts               |
| ref               |
+-------------------+
2 rows in set (0.00 sec)

Contents of each table are as follows

mysql> select * from ref;
+----+------+------+
| Id | key1 | key2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    2 |    2 |
|  4 |    3 |    1 |
|  5 |    3 |    2 |
|  6 |    3 |    3 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> select * from dts;
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |
|  5 |    1 |    2 |      5 |    0 |    0 |    0 |    2 |    5 |
|  6 |    1 |    2 |      6 |    0 |    0 |    0 |    0 |    1 |
|  7 |    1 |    2 |      7 |    0 |    1 |    0 |    0 |    0 |
|  8 |    2 |    2 |      1 |    1 |    1 |    1 |    1 |    2 |
|  9 |    2 |    2 |      2 |    0 |    0 |    0 |    0 |    0 |
| 10 |    3 |    2 |      3 |    0 |    0 |    0 |    0 |    0 |
| 11 |    3 |    3 |      1 |    1 |    1 |    0 |    0 |    1 |
| 12 |    3 |    3 |      5 |    0 |    0 |    1 |    1 |    0 |
+----+------+------+--------+------+------+------+------+------+
12 rows in set (0.00 sec)

This is what I tried to join two table

mysql> select distinct
    ->        i.key1,
    ->        i.key2 
    -> from 
    ->        ref i, 
    ->        dts d 
    -> where 
    ->        i.key1=d.key1 and 
    ->        i.key2=d.key2 ;
+------+------+
| key1 | key2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    3 |    3 |
+------+------+
5 rows in set (0.00 sec)

I am expecting below o/p, really don't know how to get it

key1       key2 fields_non_zero
1           1   pr1,pr3,pr4,pr5
1           2   pr2,pr4,pr5
2           2   pr1,pr2,pr3,pr4,pr5
3           2
3           3   pr1,pr2,pr3,pr4,pr5

I would like check using below condition for example, lets take key1=1 and key2=1 of both table matched

  1. Join two table
  2. check is there any non zero data in fields(pr1-pr5) of dts, which is matched
  3. If found concat field name with comma,
  4. suppose if all fields are non zero, just concat fields and stop joining further for same key1, key2 because if found all (save execution time), go to next key1,key2
+----+------+------+
| Id | key1 | key2 |
+----+------+------+
|  1 |    1 |    1 |              <- for ref table key1,key2 following rows matches 

| Id | key1 | key2 | serial | pr1  | pr2  | pr3  | pr4  | pr5  |  nonzero_fields 
+----+------+------+--------+------+------+------+------+------+
|  1 |    1 |    1 |      1 |    0 |    0 |    1 |    0 |    2 |  = pr3,pr5
|  2 |    1 |    1 |      2 |    0 |    0 |    0 |    0 |    0 |  =  
|  3 |    1 |    1 |      3 |    0 |    0 |    0 |    1 |    0 |  = pr4
|  4 |    1 |    1 |      4 |    1 |    0 |    1 |    1 |    3 |  = pr1,pr3,pr4,p45

  So distinct of below are

         = pr3,pr5
         = 
         = pr4
         = pr1,pr3,pr4,p45 

 key1   key2 fields_non_zero
 1       1   pr1,pr3,pr4,pr5

I don't mind atleast if I get without order like below

 key1   key2 fields_non_zero
 1       1   pr3,pr5,pr4,pr1

Structure of table is as follows

DROP TABLE IF EXISTS `dts`;
CREATE TABLE `dts` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  `serial` int(11) DEFAULT '-99',
  `pr1` int(11) DEFAULT '-99',
  `pr2` int(11) DEFAULT '-99',
  `pr3` int(11) DEFAULT '-99',
  `pr4` int(11) DEFAULT '-99',
  `pr5` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`,`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


LOCK TABLES `dts` WRITE;
INSERT INTO `dts` VALUES (1,1,1,1,0,0,1,0,2),(2,1,1,2,0,0,0,0,0),(3,1,1,3,0,0,0,1,0),(4,1,1,4,1,0,1,1,3),(5,1,2,5,0,0,0,2,5),(6,1,2,6,0,0,0,0,1),(7,1,2,7,0,1,0,0,0),(8,2,2,1,1,1,1,1,2),(9,2,2,2,0,0,0,0,0),(10,3,2,3,0,0,0,0,0),(11,3,3,1,1,1,0,0,1),(12,3,3,5,0,0,1,1,0);
UNLOCK TABLES;



DROP TABLE IF EXISTS `ref`;
CREATE TABLE `ref` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` int(11) DEFAULT '-99',
  `key2` int(11) DEFAULT '-99',
  PRIMARY KEY (`Id`),
  KEY `main` (`key1`,`key2`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


LOCK TABLES `ref` WRITE;
INSERT INTO `ref` VALUES (1,1,1),(2,1,2),(3,2,2),(4,3,1),(5,3,2),(6,3,3);
UNLOCK TABLES;
Solution

  • You can unpivot the data from dts table and then use group_concat on it.

    SELECT 
    r.key1,
    r.key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM
    ref r
        INNER JOIN
    (
    SELECT
    d.key1,
    d.key2,
    t.pr,
    CASE t.pr
        WHEN 'pr1' THEN pr1
        WHEN 'pr2' THEN pr2
        WHEN 'pr3' THEN pr3
        WHEN 'pr4' THEN pr4
        WHEN 'pr5' THEN pr5
    END val
FROM
    dts d
        CROSS JOIN
    (
    SELECT 'pr1' pr UNION ALL 
    SELECT 'pr2' UNION ALL 
    SELECT 'pr3' UNION ALL 
    SELECT 'pr4' UNION ALL 
    SELECT 'pr5') t
    ) d ON r.key1 = d.key1 AND r.key2 = d.key2
GROUP BY r.key1 , r.key2;

    Produces:

    +------+------+---------------------+
| key1 | key2 | prs                 |
+------+------+---------------------+
|    1 |    1 | pr1,pr3,pr4,pr5     |
|    1 |    2 | pr2,pr4,pr5         |
|    2 |    2 | pr1,pr2,pr3,pr4,pr5 |
|    3 |    2 | NULL                |
|    3 |    3 | pr1,pr2,pr3,pr4,pr5 |
+------+------+---------------------+
5 rows in set (0.00 sec)

    EDIT:

    Without joining with ref table (since ref table has all the key1, key2 and we were just inner joining with it):

    SELECT 
    key1,
    key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM (
    SELECT 
        d.key1,
            d.key2,
            t.pr,
            CASE t.pr
                WHEN 'pr1' THEN pr1
                WHEN 'pr2' THEN pr2
                WHEN 'pr3' THEN pr3
                WHEN 'pr4' THEN pr4
                WHEN 'pr5' THEN pr5
            END val
    FROM
        dts d
    CROSS JOIN (
        SELECT 'pr1' pr UNION ALL 
        SELECT 'pr2' UNION ALL 
        SELECT 'pr3' UNION ALL 
        SELECT 'pr4' UNION ALL 
        SELECT 'pr5'
    ) t
) r
GROUP BY key1 , key2;

    Produces same output:

    +------+------+---------------------+
| key1 | key2 | prs                 |
+------+------+---------------------+
|    1 |    1 | pr1,pr3,pr4,pr5     |
|    1 |    2 | pr2,pr4,pr5         |
|    2 |    2 | pr1,pr2,pr3,pr4,pr5 |
|    3 |    2 | NULL                |
|    3 |    3 | pr1,pr2,pr3,pr4,pr5 |
+------+------+---------------------+
5 rows in set (0.00 sec)

    EDIT 2:

    SELECT 
    r.key1,
    r.key2,
    group_concat(distinct case when val > 0 then pr end order by pr separator ',') prs
FROM (
    select key1, key2
    from ref
    order by id
    limit 0, 1000       -- Added limit to get only first 1000 key pairs based on id
) r INNER JOIN (
    SELECT
    d.key1,
    d.key2,
    t.pr,
    CASE t.pr
        WHEN 'pr1' THEN pr1
        WHEN 'pr2' THEN pr2
        WHEN 'pr3' THEN pr3
        WHEN 'pr4' THEN pr4
        WHEN 'pr5' THEN pr5
    END val
FROM
    dts d
        CROSS JOIN
    (
    SELECT 'pr1' pr UNION ALL 
    SELECT 'pr2' UNION ALL 
    SELECT 'pr3' UNION ALL 
    SELECT 'pr4' UNION ALL 
    SELECT 'pr5') t
    ) d ON r.key1 = d.key1 AND r.key2 = d.key2
GROUP BY r.key1 , r.key2;

    For first 1000 unique key pairs, use below SQL in the above query:

    (
    select key1, key2
    from ref
    group by key1, key2
    order by key1, key2
    limit 0, 1000       -- Added limit to get only first 1000 key pairs based on id
) r