Jump to content
#StayAtHome ×
Yohanne

MYSQL GROUP_CONCAT IS NOT WORKING

Recommended Posts

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                  |   
+-----------------------------------------------------------+

 

Share this post


Link to post
Share on other sites
Posted (edited)

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

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

Edited by Barand

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Yohanne

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Yohanne

Share this post


Link to post
Share on other sites

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 |
+---------------+------+--------------------------+

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Yohanne

Share this post


Link to post
Share on other sites
1 hour ago, Barand said:

FROM table3

how about this? can i have your query..

Share this post


Link to post
Share on other sites

i mean is. disregard the table3 at all. 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

"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.

 

Share this post


Link to post
Share on other sites

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 

Share this post


Link to post
Share on other sites

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

image.png.e34161324b272545b21a74b7266e36cc.png

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.