Jump to content

MYSQL GROUP_CONCAT IS NOT WORKING


Yohanne

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

 

Link to comment
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.

Link to comment
Share on other sites

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
Link to comment
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 |
+---------------+------+--------------------------+

 

Link to comment
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

 

Link to comment
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.

 

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.