MYSQL GROUP_CONCAT IS NOT WORKING

[Yohanne]

Hi coders,

this is not the actual of my table but the scenario is the same..  i cannot get the list of the item  because  conflict, im not sure.,  i try it many time  10hrs  but failed.  now i decided to get help from you guys..

 

Table1
+-----------------------------------------+  
|   id  |  item_id  | paymen | payment_id | 
+-----------------------------------------+
|  1    |     1     |  25    |     11     |
+-----------------------------------------+
|  2    |     3     |  15    |     11     |
+-----------------------------------------+
|  3    |     2     |  75    |     11     |
+-----------------------------------------+
|  4    |     5     |  100   |     2      |
+-----------------------------------------+
|  5    |     1     |  88    |     1      |
+-----------------------------------------+
|  6    |     1     |  25    |     2      |
+-----------------------------------------+

table2
+-------------------------+
|  item_id  |  item_name  | 
+-------------------------+
|     1     |  RH Beer    |    
+-------------------------+
|     3     |  22Wine     |     
+-------------------------+
|     2     |  Iphone4    | 
+-------------------------+
|     5     |  Nokia3210  |    
+-------------------------+

table3
+-----------------------------------+  
|   id  |   name       | payment_id | 
+-----------------------------------+
|  1    |   Lim        |     11     |
+-----------------------------------+
|  2    |   Lim        |     11     |  
+-----------------------------------+
|  3    |   Lim        |     11     |   
+-----------------------------------+
|  4    |   Jhon       |     2      | 
+-----------------------------------+
|  5    |   Leo        |     1      |  
+-----------------------------------+
|  6    |   Jhon       |     2      |  
+-----------------------------------+




out put should be like this if possible 
+-----------------------------------------------------------+  
|   payment total  |   name    |           item             | 
+-----------------------------------------------------------+
|       115        |   Lim     |   RH Beer, 22Wine, Iphone4 |
+-----------------------------------------------------------+
|       125        |   Jhon    |   RH Beer, Nokia3210       |  
+-----------------------------------------------------------+
|       88         |   Leo     |   RH Beer                  |   
+-----------------------------------------------------------+

 

[Barand]

You forgot to show your query where "group_concat is not working"

EDIT: That peculiar data model that you have needs work.

Edited March 8, 2020 by Barand

[Yohanne]

the query is messy actually an very long, that why i didn't show. there are many table join. my forpus is to get an idea if this case is possible..

Edited March 8, 2020 by Yohanne

[Barand]

The table1 - table2 relationship is fine but each row in table1 matches several rows in table3 instead of just one.

As this is hypothetical case (don't know what it represents)  there is no way to know why the weird structure of table3 and the purpose of the payment_id columns in tables 1 and 3.

Those relationships won't give what you want without a messy query.

[Yohanne]

im not the creator of this db. im a second user actually.  i am now creating a report and i found this case.. and im stock here.  but do you think theres is no  way to achieve this case. since the client want that case...   

Edited March 8, 2020 by Yohanne

[Barand]

Tell the creator to Google "database normalization" and try an couple of tutorials.

If you use a subquery which effectively puts table3 in the normalized form it should've had, which is

Table3
+------+------------+
| name | payment_id |
+------+------------+
| Lim  |         11 |
| Jhon |          2 |
| Leo  |          1 |
+------+------------+

then it works. Without the subquery it doubles or trebles the totals and item lists.

SELECT SUM(payment) as payment_total
     , name
     , GROUP_CONCAT(item_name SEPARATOR ', ') as items
FROM table1 
     JOIN table2 USING (item_id)
     JOIN (
            SELECT DISTINCT 
                   name
                 , payment_id
            FROM table3     
        ) t3 USING (payment_id)
GROUP BY payment_id;

+---------------+------+--------------------------+
| payment_total | name | items                    |
+---------------+------+--------------------------+
|            88 | Leo  | RH Beer                  |
|           125 | Jhon | Nokia3210, RH Beer       |
|           115 | Lim  | 22Wine, RH Beer, IPhone4 |
+---------------+------+--------------------------+

 

[Yohanne]

wow . . Thank you last one question, how to achieve if i only use table1 and table2 and display the item_name in group_concat. base on payment_id

Edited March 8, 2020 by Yohanne

[Barand]

Remove references to table3 from the query.

[Yohanne]

1 hour ago, Barand said:

FROM table3

how about this? can i have your query..

[Barand]

I posted it an hour ago

[Yohanne]

i mean is. disregard the table3 at all. 

[Barand]

What you mean is "will I do all your thinking for you?"

[Yohanne]

here is my query 

- trtpay.treatment_payment_id is already group_by

- procedure_id is represent as item _id and  treatement_payment_id is payment_id

but here i get error like syntax to use near 'distinct(treatement_payment_id)as.. 

left join (select procedure_id, distinct(treatement_payment_id)as tp from episode_payment) as episode 
on episode.tp = trtpay.treatment_payment_id

 

[Barand]

"distinct" is a keyword, not a function. You cannot apply it to a single column.

Quote

The ALL and DISTINCT modifiers specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both modifiers. DISTINCTROW is a synonym for DISTINCT.

 

[Yohanne]

so what is the correct syntax for it. i need procedure_id not to distinct  because i need procedure_id to get the details of table2 

[Barand]

If you don't use table3 you need to remove these bits...

The resulting query gives

+---------------+--------------------------+
| payment_total | items                    |
+---------------+--------------------------+
|            88 | RH Beer                  |
|           125 | RH Beer, Nokia3210       |
|           115 | 22Wine, IPhone4, RH Beer |
+---------------+--------------------------+

Of course you can't tell to whom each set of items belong as the name came from table3