Select distinct on Left Join for only one column

Nick Published at Dev

nick

I'm trying to list products that each customer bought, but if they bought the same item on different occasions, I want it to exclude it. This is what I have so far:

Select  c.field_id_33  AS email, o.order_id, Group_concat(o.entry_id) AS Products,group_concat(t.title),group_concat(t.url_title) from finn_cartthrob_order_items o  
LEFT JOIN finn_channel_data c
ON c.entry_id=o.order_id
LEFT JOIN finn_channel_titles t
ON o.entry_id=t.entry_id
GROUP BY email

This is producing:

screenshot

Basically I only need a product listed one time if they've purchased it, no matter how many times they've purchased it. How would I do this?

M Khalid Junaid

You can use DISTINCT in group_concat function,using Group_concat baware of that fact it has a default limit of 1024 characters to group them but it can be increased

Select  c.field_id_33  AS email, o.order_id, 
Group_concat(DISTINCT o.entry_id) AS Products,
group_concat(DISTINCT t.title),
group_concat(DISTINCT t.url_title)
 from finn_cartthrob_order_items o  
LEFT JOIN finn_channel_data c
ON c.entry_id=o.order_id
LEFT JOIN finn_channel_titles t
ON o.entry_id=t.entry_id
GROUP BY email

From the docs The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Collected from the Internet

Please contact [email protected] to delete if infringement.