Web lists-archives.com

Re: Query Help...






On 10/20/2015 1:54 PM, Don Wieland wrote:
Hi all,

Trying to get a query working:

SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS alloc

FROM hiv_transactions ht

LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id

WHERE ht.donor_id = 730 AND ht.tr_date BETWEEN "2014-01-01 00:00:00" AND "2014-12-31 23:59:59"
ORDER BY ht.tr_date DESC, ht.rec_code ASC;



I am only showing one row of the “hiv_transactions” table when there are multiple rows.

On the GROUP_CONCAT I am trying to get a comma delineated list of the child rec_code with no duplicates

Appreciate any help. Hopefully a small mod ;-)


Don Wieland


Which release of MySQL are you using?

How many rows do you get if you remove the GROUP_CONCAT operator? We don't need to see the results. (sometimes it is a good idea to look at the raw, unprocessed results)

Is it possible that you are attempting to concat more values than allowed by --group-concat-max-len ?

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql