Web lists-archives.com

Re: Query Help...




On 2015-10-20 12: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 <http://ht.tr/>_date BETWEEN "2014-01-01 00:00:00" AND "2014-12-31 23:59:59"
ORDER BY ht.tr <http://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 ;-)

Group_Concat() is an aggregating function, so you need to Group By the column(s) on which you wish to aggregate, and for valid results you need to limit Selected columns to those on which you're aggregating plus those columns that have unique values for your aggregating columns..

PB

----


Don Wieland
D W   D a t a


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