mariadb complex combine table columns based on common columns and add new column when not found in common columns on output

Question

I have 3 tables like below. In which id, fcd, ref columns are common across table, I am trying to combine all tables columns table into single table.

MariaDB [test]> select * from t1;
+----+-----+-----+------+------+
| id | fcd | ref | c1   | c2   |
+----+-----+-----+------+------+
|  1 |   1 | 1.0 | 0.10 | 2.00 |
|  1 |   2 | 1.5 | 0.40 | 2.22 |
|  3 |   1 | 2.0 | 0.10 | 4.30 |
|  3 |   2 | 3.2 | 0.01 | 6.60 |
|  5 |   3 | 7.5 | 0.00 | 7.70 |
+----+-----+-----+------+------+
5 rows in set (0.001 sec)

MariaDB [test]> select * from t2;
+----+-----+------+------+------+
| id | fcd | ref  | c3   | c4   |
+----+-----+------+------+------+
|  1 |   1 | 1.00 | 0.06 | 0.76 |
|  1 |   2 | 1.20 | 0.32 | 0.87 |
|  5 |   2 | 6.50 | 0.44 | 0.09 |
|  5 |   3 | 6.68 | 0.47 | 0.08 |
+----+-----+------+------+------+
4 rows in set (0.001 sec)

MariaDB [test]> select * from t3;
+----+-----+-----+------+------+
| id | fcd | ref | c5   | c6   |
+----+-----+-----+------+------+
|  1 |   3 | 1.1 | 0.02 | 0.12 |
|  1 |   4 | 2.0 | 0.23 | 0.11 |
|  7 |   1 | 3.2 | 0.45 | 0.43 |
|  7 |   2 | 7.5 | 0.54 | 0.67 |
+----+-----+-----+------+------+
4 rows in set (0.000 sec)

This is what I tried using union no luck

MariaDB [test]> select id,fcd,ref,c1,c2,null c3, null c4, null c5, null c6 from t1  union all  select id,fcd,ref,null c1, null c2, c3, c4,null c5, null c6 from t2 union all select id,fcd,ref,null c1, null c2, null c3, null c4,c5, c6 from t3 order by id, fcd, ref;
+----+-----+------+------+------+------+------+------+------+
| id | fcd | ref  | c1   | c2   | c3   | c4   | c5   | c6   |
+----+-----+------+------+------+------+------+------+------+
|  1 |   1 | 1.00 | NULL | NULL | 0.06 | 0.76 | NULL | NULL |
|  1 |   1 | 1.00 | 0.10 | 2.00 | NULL | NULL | NULL | NULL |
|  1 |   2 | 1.20 | NULL | NULL | 0.32 | 0.87 | NULL | NULL |
|  1 |   2 | 1.50 | 0.40 | 2.22 | NULL | NULL | NULL | NULL |
|  1 |   3 | 1.10 | NULL | NULL | NULL | NULL | 0.02 | 0.12 |
|  1 |   4 | 2.00 | NULL | NULL | NULL | NULL | 0.23 | 0.11 |
|  3 |   1 | 2.00 | 0.10 | 4.30 | NULL | NULL | NULL | NULL |
|  3 |   2 | 3.20 | 0.01 | 6.60 | NULL | NULL | NULL | NULL |
|  5 |   2 | 6.50 | NULL | NULL | 0.44 | 0.09 | NULL | NULL |
|  5 |   3 | 6.68 | NULL | NULL | 0.47 | 0.08 | NULL | NULL |
|  5 |   3 | 7.50 | 0.00 | 7.70 | NULL | NULL | NULL | NULL |
|  7 |   1 | 3.20 | NULL | NULL | NULL | NULL | 0.45 | 0.43 |
|  7 |   2 | 7.50 | NULL | NULL | NULL | NULL | 0.54 | 0.67 |
+----+-----+------+------+------+------+------+------+------+
13 rows in set (0.001 sec)

above one hasn't combined correctly as you can see for 1 | 1 | 1 there is duplicate

this is what I expect

| id    | fcd   | ref   | c1    | c2    | c3    | c4    | c5    | c6    |
|----   |-----  |------ |------ |------ |------ |------ |------ |------ |
| 1     | 1     | 1     | 0.1   | 2     | 0.06  | 0.76  | NULL  | NULL  |
| 1     | 2     | 1.2   | NULL  | NULL  | 0.32  | 0.87  | NULL  | NULL  |
| 1     | 2     | 1.5   | 0.4   | 2.22  | NULL  | NULL  | NULL  | NULL  |
| 1     | 3     | 1.1   | NULL  | NULL  | NULL  | NULL  | 0.02  | 0.12  |
| 1     | 4     | 2     | NULL  | NULL  | NULL  | NULL  | 0.23  | 0.11  |
| 3     | 1     | 2     | 0.1   | 4.3   | NULL  | NULL  | NULL  | NULL  |
| 3     | 2     | 3.2   | 0.01  | 6.6   | NULL  | NULL  | NULL  | NULL  |
| 5     | 2     | 6.5   | NULL  | NULL  | 0.44  | 0.09  | NULL  | NULL  |
| 5     | 3     | 6.68  | NULL  | NULL  | 0.47  | 0.08  | NULL  | NULL  |
| 5     | 3     | 7.5   | 0     | 7.7   | NULL  | NULL  | NULL  | NULL  |
| 7     | 1     | 3.2   | NULL  | NULL  | NULL  | NULL  | 0.45  | 0.43  |
| 7     | 2     | 7.5   | NULL  | NULL  | NULL  | NULL  | 0.54  | 0.67  |

Please someone help me, don't know how to solve this

score 2 · Accepted Answer · answered Jul 02 '21 at 18:52

2

You can group by id,fcd,ref on the results of your query and aggregate:

select id,fcd,ref,max(c1) c1,max(c2) c2,max(c3) c3,max(c4) c4,max(c5) c5,max(c6) c6 
from (  
  select id,fcd,ref,c1,c2,null c3, null c4, null c5, null c6 from t1  
  union all  
  select id,fcd,ref,null c1, null c2, c3, c4,null c5, null c6 from t2 
  union all 
  select id,fcd,ref,null c1, null c2, null c3, null c4,c5, c6 from t3 
) t
group by id,fcd,ref
order by id,fcd,ref;

See the demo.

answered Jul 02 '21 at 18:52

forpas

160,666
10
38
76

Your solution also works very well thank you very much, I selected other one as that was made without specifying null explicitly. – Helen Jul 03 '21 at 14:21
@Helen I'm surprised that you prefer a solution with UNION which is less performant than UNION ALL and also 3 LEFT joins. – forpas Jul 03 '21 at 14:22
I am really not getting which is more efficient, if I create unique index on id,fcd,ref your solution produces results with very little work, but throws me some warning, please guide me which is more efficient when on large table – Helen Jul 03 '21 at 15:20
with index being `create unique index myind on t1 (id,fcd,ref); create unique index myind on t2 (id,fcd,ref); create unique index myind on t3 (id,fcd,ref);` on all 3 tables `explain extended` I am not understanding why this warning is coming – Helen Jul 03 '21 at 15:25
@Helen Warnings are not errors: https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html Also, test both queries and decide about performance, maintenance and scalability. – forpas Jul 03 '21 at 15:32
Yes read now, nothing to worry then right ? your solution in explain solved in just 4 rows, other one using 8 rows. Thanks a lot for guiding me – Helen Jul 03 '21 at 15:37
I got one more problem, will you please help me ? https://stackoverflow.com/questions/68244625/mariadb-state-wise-where-case-clause-inside-view – Helen Jul 04 '21 at 12:17
Just FYI my other answer was just showing another option. It may be less performance as `UNION` incurs a performance hit (in the same way as `GROUP BY`). I totally agree with @fopas on assessing on performance, maintenance and scalability. Performance assessment needs to be a be on a realistic dataset and including timing formation, going of number of `explain` rows isn't a measure, use [ANALYZE FORMAT=JSON](https://mariadb.com/kb/en/analyze-formatjson-examples/) and look at the [r_total_time_ms](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=05b14c05a7d4e7bcb4be9c0fd0540c1e) . – danblack Jul 05 '21 at 00:30

score 2 · Answer 2 · answered Jul 03 '21 at 03:08

2

By using UNION instead of UNION ALL on id, fcd, ref columns you could arrive at the same result with:

SELECT t.id,
       t.fcd,
       t.ref,
       t1.c1,
       t1.c2,
       t2.c3,
       t2.c4,
       t3.c5,
       t3.c6
FROM
  (SELECT id,
          fcd,
          ref
   FROM t1
   UNION SELECT id,
                fcd,
                ref
   FROM t2
   UNION SELECT id,
                fcd,
                ref
   FROM t3) AS t
LEFT JOIN t1 USING(id,
                   fcd,
                   ref)
LEFT JOIN t2 USING(id,
                   fcd,
                   ref)
LEFT JOIN t3 USING(id,
                   fcd,
                   ref)
ORDER BY id,
         fcd,
         ref;

ref: fiddle (thanks @forpas for base data)

answered Jul 03 '21 at 03:08

danblack

12,130
2
22
41

This works, can you explain left join part ? I did add base data but don't know why its migrated – Helen Jul 03 '21 at 06:00
You simplified the query by removing `null as c1` and so on thank you – Helen Jul 03 '21 at 06:25
The result of the left join is when a row isn't matched, the fields are returned as null. – danblack Jul 03 '21 at 13:33

mariadb complex combine table columns based on common columns and add new column when not found in common columns on output

2 Answers2