Joe Richard Joe Richard - 29 days ago 5
Android Question

Union of rows if specific column value is duplicated

For example I have 3 tables:


table1(id,value) table2(id,fk,value) table3(id,fk,value)


table2 and table3 are children of table1.

I have following SQL query:

query = "SELECT" +
" t1.id AS _id," +
" t2.id AS t2_id," +
" t2.fk as t2_fk," +
" t2.value as t2_value," +
" t3.id AS t3_id," +
" t3.fk as t3_fk," +
" t3.value as t3_value," +
" FROM t1 table1" +
" INNER JOIN table2 t2 ON t1.id = t2.fk" +
" INNER JOIN table3 t3 ON t1.id = t3.fk" +
" WHERE t2.value like '%"+lookingFor+"%' LIMIT 10";


Cursor is giving following result:

enter image description here

How to make like this:

enter image description here

My intention is to avoid duplication of values and at the same time collect all t3_value in one place. How might I achieve this?

Answer

Try this:

query = "SELECT" +
            " t1.id AS _id," +
            " t2.id AS t2_id," +
            " t2.fk as t2_fk," +
            " t2.value as t2_value," +
            " GROUP_CONCAT(t3.value) as t3_value," +
            " FROM t1 table1" +
            "     INNER JOIN table2 t2 ON t1.id = t2.fk" +
            "     INNER JOIN table3 t3 ON t1.id = t3.fk" +
            " WHERE t2.value like '%"+lookingFor+"%'
            GROUP BY t1.id, t2.id, t2.fk, t2.value  LIMIT 10 ";

and understand that what you want is "GROUP_CONCAT(X,Y)" - partial reference here.