Mysql – How MySql Join two tables with Checkboxes results stored as “string” data format

join;MySQLmysql-5.7

Suppose I have 2 tables obtained from a web form submission, with some checkboxes results stored as "string" format as describerd here in "hobby" column https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=aff4e50085be40930e223b00a1ac81f3,

How could I join this two tables and obtain the following result?

sub_id	datimkt
1	Basket,Gas,SUV
2	Basket,Cabrio,Football,hybrid

I've tried to execute the following query, but it only takes the first result from the checkbox result field.

SELECT GROUP_CONCAT(DISTINCT submission_id)sub_id,
GROUP_CONCAT(DISTINCT c1.option_name)datimkt
FROM form_results d
JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt)
GROUP BY d.datimkt;
SELECT*FROM result2;

Best Answer

You need to group by the submission_id:

like:

SELECT submission_id sub_id,
GROUP_CONCAT(DISTINCT c1.option_name)datimkt
FROM form_results d
JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt)
GROUP BY sub_id;

Related Solutions

Mysql – Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

JOINs together with aggregates (eg, SUM()) often give you bigger values than you expect. To see this, remove the SUMs and GROUP BY and stare at the output. You may see, for example, the same mghours.hourstime showing up multiple times. Hence, SUM(mghours.hourstime) is inflated.

This is happening because JOIN mgtraining is coming up with multiple rows in mgtraining for each row in mghours.

The solution is to do the query in steps. Notice how the inner query avoids inflating "vol":

SELECT  x.*,
        SUM(...) AS 'at
    FROM (
        SELECT  ...,
                SUM(mghours.hourstime) AS vol
            FROM ...
            GROUP BY ...
         ) x
    LEFT JOIN mgtraining ON ...
    GROUP BY ...
    ORDER BY ...;

Mysql – How to JOIN two tables with substring

The leading wildcard in the like, not the function, is preventing the use of the table1.col1 index (assuming it is indexed).

For an indexed approach, the values can be store reversed...

update table1 set col1=reverse(col1);
alter table1 add key (col1);  -- if not already indexed

update table2 set col2=reverse(col2);

update table1 a
join table2 b    on a.col1 like concat(b.col2,'.%')
set a.foreign_id=b.foreign_id;

Of course, this comes with the downside that the column will need to be reversed for display...

select reverse(col1) from table1 where...

Here is a similar denormalized approach with the reversed value redundantly stored in the same table...

alter table table1 add column col1_rev -- col1 datatype specification here;
update table1 set col1_rev=reverse(col1);
alter table table1 add key (col1_rev);

alter table table2 add column col2_rev -- col2 datatype specification here;
update table2 set col2_rev=reverse(col2);
alter table table2 add key (col2_rev);

update table1 a
  join table2 b on a.col1_rev like concat(b.col2_rev,'.%')
    set a.foreign_id=b.foreign_id;

A normalized approach would be another table of unique table1.col1 values as stored and reversed...

create table table1_col1_rev as
select distinct col1
     , reverse(col1) as col1_rev
  from table1;

alter table table1_col1_rev
  add key (col1)
        , (col1_rev);

update table1 a
     , table1_col1_rev b
     , table2 c
   set a.foreign_id=c.foreign_id
 where b.col1_rev like reverse(concat('%.',c.col2))
   and a.col1=b.col1;

Best Answer

Related Solutions

Mysql – Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

Mysql – How to JOIN two tables with substring

Related Question