CODEWITHSUNDEEP
mysqlsqlunion
DarkKnightFan
DarkKnightFan

Reputation: 1953

mysql: merge two records into one

I have the below result set from my query (which has a UNION of two SELECT queries)

app_id  transaction_id  mobile_no   user_input1                          user_input3                           user_input17                     user_input110                     user_input61            user_input62
104     731           918087318987  Welcome To Onida~Home Appliances    Home Appliances~Washing Machine Washing Machine~Fully Automatic Fully Automatic~SPARKLE 65S                NULL                  NULL
104     731           918087318987      NULL                               NULL                                     NULL                            NULL                           Quantity ~2                Amount~2

I want to merge these two records into 1 row

app_id  transaction_id  mobile_no   user_input1                          user_input3                           user_input17                     user_input110                     user_input61            user_input62
104     731           918087318987  Welcome To Onida~Home Appliances    Home Appliances~Washing Machine Washing Machine~Fully Automatic Fully Automatic~SPARKLE 65S           Quantity ~2                Amount~2

I can provide my SQL query but it is too long. Will provide if required.

SQLSchema:

create table `trn_user_log` (
`app_id` int (11),
`transaction_id` int (11),
`mobile_no` varchar (45),
`node_id` bigint (20),
`customer_attribute` varchar (150),
`entered_value` varchar (150)
); 

insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','103','Welcome To Onida','2');
insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','105','Home Appliances','1');
insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','119','Washing Machine','1');
insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','121','Fully Automatic','2');
insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','169','Quantity ','2');
insert into `trn_user_log` (`app_id`, `transaction_id`, `mobile_no`, `node_id`, `customer_attribute`, `entered_value`) values('104','731','918087318987','170','Amount','2');


create table `mst_node` (
`app_id` int (11),
`node_id` bigint (20),
`parent_node_id` bigint (20),
`display_seq` tinyint (4),
`display_text` varchar (540),
`customer_attribute` varchar (150)
);

insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','103',NULL,'1','Welcome To Onida','Welcome To Onida');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','105','103','2','Home Appliances','Home Appliances');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','119','105','1','Washing Machine','Washing Machine');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','121','119','1','Fully Automatic','Fully Automatic');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','124','121','2','SPARKLE 65S ','SPARKLE 65S ');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','125','121','3','Sparkle 65X','Sparkle 65X');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','126','121','4','Sparkle 62P','Sparkle 62P');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','169','124','1','Quantity ','Quantity ');
insert into `mst_node` (`app_id`, `node_id`, `parent_node_id`,  `display_seq`, `display_text`,  `customer_attribute`) values('104','170','124','2','Amount','Amount');

My SQL Query:

SELECT *
FROM
(
SELECT T1.app_id,
   T1.transaction_id,
   T1.mobile_no,
   CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 103 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input1,
      CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 105 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input3,
      CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 119 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input17,
      CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 121 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input110,
   CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 169 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input61,
   CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 170 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input62
FROM trn_user_log T1 INNER JOIN mst_node T2 ON T1.APP_ID = T2.APP_ID
 WHERE     T1.app_id = 104
   AND T1.transaction_id = 731
   AND T1.node_id = T2.parent_node_id
   AND T2.`display_seq` = T1.entered_value
--   GROUP BY T1.app_id, T1.transaction_id, T1.mobile_no
--  ORDER BY T1.node_id

UNION 

SELECT T3.app_id,
   T3.transaction_id,
   T3.mobile_no,
   CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 103 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input1,
      CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 105 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input3,
      CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 119 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input17,
      CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 121 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input110,
   CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 169 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input61,
   CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 170 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input62
FROM trn_user_log T3 INNER JOIN mst_node T4 ON T3.APP_ID = T4.APP_ID
WHERE  T3.app_id = 104
   AND T3.transaction_id = 731
   AND T3.node_id = T4.node_id
   AND  not exists (select 1 from mst_node b where b.parent_node_id = t4.node_id)

--        GROUP BY T3.app_id, T3.transaction_id, T3.mobile_no
-- ORDER BY T3.node_id
)T 
GROUP BY T.app_id, T.transaction_id, T.mobile_no

SQLFiddle Demo Schema and Query

Upvotes: 2

Views: 3850

Answers (3)

vearutop
vearutop

Reputation: 4062

You can JOIN two selects with each other and then perform COALESCE on columns.

http://www.sqlfiddle.com/#!2/bfcd8/15

SELECT app_id,transaction_id,mobile_no,
coalesce(tt1.USER_INPUT1,tt2.USER_INPUT1) AS USER_INPUT1,
coalesce(tt1.USER_INPUT3,tt2.USER_INPUT3) AS USER_INPUT3,
coalesce(tt1.USER_INPUT17,tt2.USER_INPUT17) AS USER_INPUT17,
coalesce(tt1.USER_INPUT110,tt2.USER_INPUT110) AS USER_INPUT110,
coalesce(tt1.USER_INPUT61,tt2.USER_INPUT61) AS USER_INPUT61,
coalesce(tt1.USER_INPUT62,tt2.USER_INPUT62) AS USER_INPUT62
 FROM (SELECT T1.app_id,
       T1.transaction_id,
       T1.mobile_no,
       CONVERT(
          GROUP_CONCAT(
             (CASE T1.node_id
                 WHEN 103 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input1,
          CONVERT(
          GROUP_CONCAT(
             (CASE T1.node_id
                 WHEN 105 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input3,
          CONVERT(
          GROUP_CONCAT(
             (CASE T1.node_id
                 WHEN 119 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input17,
          CONVERT(
          GROUP_CONCAT(
             (CASE T1.node_id
                 WHEN 121 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input110,
       CONVERT(
          GROUP_CONCAT(
             (CASE T1.node_id
                 WHEN 169 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input61,
       CONVERT(
          GROUP_CONCAT(
             (CASE T1.node_id
                 WHEN 170 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input62
  FROM trn_user_log T1 INNER JOIN mst_node T2 ON T1.APP_ID = T2.APP_ID
 WHERE     T1.app_id = 104
       AND T1.transaction_id = 731
       AND T1.node_id = T2.parent_node_id
       AND T2.`display_seq` = T1.entered_value
--   GROUP BY T1.app_id, T1.transaction_id, T1.mobile_no
--  ORDER BY T1.node_id
) AS tt1

LEFT JOIN

(SELECT T3.app_id,
       T3.transaction_id,
       T3.mobile_no,
       CONVERT(
          GROUP_CONCAT(
             (CASE T3.node_id
                 WHEN 103 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input1,
          CONVERT(
          GROUP_CONCAT(
             (CASE T3.node_id
                 WHEN 105 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input3,
          CONVERT(
          GROUP_CONCAT(
             (CASE T3.node_id
                 WHEN 119 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input17,
          CONVERT(
          GROUP_CONCAT(
             (CASE T3.node_id
                 WHEN 121 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input110,
       CONVERT(
          GROUP_CONCAT(
             (CASE T3.node_id
                 WHEN 169 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input61,
       CONVERT(
          GROUP_CONCAT(
             (CASE T3.node_id
                 WHEN 170 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
                 ELSE NULL
              END)) USING LATIN1)
          AS user_input62
FROM trn_user_log T3 INNER JOIN mst_node T4 ON T3.APP_ID = T4.APP_ID
WHERE  T3.app_id = 104
       AND T3.transaction_id = 731
       AND T3.node_id = T4.node_id
       AND  NOT EXISTS (SELECT 1 FROM mst_node b WHERE b.parent_node_id = t4.node_id)

--        GROUP BY T3.app_id, T3.transaction_id, T3.mobile_no
-- ORDER BY T3.node_id
  ) AS tt2 USING (app_id, transaction_id, mobile_no)

Upvotes: 2

Barmar
Barmar

Reputation: 780798

SELECT app_id, transaction_id, mobile_no,
       MAX(user_input1) user_input1, MAX(user_input3) user_input3,
       MAX(user_input17) user_input17, MAX(user_input110) user_input110,
       MAX(user_input61) user_input61, MAX(user_input62) user_input62
FROM (
SELECT T1.app_id,
   T1.transaction_id,
   T1.mobile_no,
   CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 103 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input1,
      CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 105 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input3,
      CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 119 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input17,
      CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 121 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input110,
   CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 169 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input61,
   CONVERT(
      GROUP_CONCAT(
         (CASE T1.node_id
             WHEN 170 THEN CONCAT(T1.customer_attribute, '~', T2.display_text)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input62
FROM trn_user_log T1 INNER JOIN mst_node T2 ON T1.APP_ID = T2.APP_ID
 WHERE     T1.app_id = 104
   AND T1.transaction_id = 731
   AND T1.node_id = T2.parent_node_id
   AND T2.`display_seq` = T1.entered_value
--   GROUP BY T1.app_id, T1.transaction_id, T1.mobile_no
--  ORDER BY T1.node_id

UNION 

SELECT T3.app_id,
   T3.transaction_id,
   T3.mobile_no,
   CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 103 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input1,
      CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 105 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input3,
      CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 119 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input17,
      CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 121 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input110,
   CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 169 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input61,
   CONVERT(
      GROUP_CONCAT(
         (CASE T3.node_id
             WHEN 170 THEN CONCAT(T3.customer_attribute, '~', T3.entered_value)
             ELSE NULL
          END)) USING LATIN1)
      AS user_input62
FROM trn_user_log T3 INNER JOIN mst_node T4 ON T3.APP_ID = T4.APP_ID
WHERE  T3.app_id = 104
   AND T3.transaction_id = 731
   AND T3.node_id = T4.node_id
   AND  not exists (select 1 from mst_node b where b.parent_node_id = t4.node_id)

--        GROUP BY T3.app_id, T3.transaction_id, T3.mobile_no
-- ORDER BY T3.node_id
)T 
GROUP BY T.app_id, T.transaction_id, T.mobile_no

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Wrap you union and a subquery then join it.

SELECT *
FROM
(
  -- place your unioned query 
) a
GROUP BY a.app_id, a.transaction_id, a.mobile_no

Upvotes: 0

Related Questions