asked on

MySql concat help needed

select
	DATE(quotes.create_date) as create_date,
	quotes.year,
    quotes.make,
    quotes.model,
    quotes.type,
    quotes.city_org,
    quotes.prov_org,
    quotes.city_dest,
    quotes.prov_dest,
	quotes.quote_amount,
	quotes.origin_shipping_type,
    quotes.dest_shipping_type,
    quotes.order_note,
    notes.note_text
from notes
join quotes on notes.quote_no=quotes.quote_no
where 
(
	quotes.quote_type = "EN"
	OR notes.note_text like "%non runner%"
	OR notes.note_text like "%nr%"
    )
AND notes.note_text like "(by:%"
AND notes.note_text not like "(by: Roger) T%"
AND notes.note_text not like "(by: Doug) T%"
AND notes.note_text not like "(by: Larry) T%"
AND notes.note_text not like "%Quote Source%"
AND DATE(quotes.create_date) >= '2017-01-01'

Open in new window

How can I concatinate the note_text so there is only a single row with concatinated notes instead of duplicate rows with different note text?

Sample output attached, I'm trying to get the notes (last column in attached image) concatinated and only 1 row returned instead of 2 and the notes column should show "(by: Steve) non runner, (by: Steve) 3300" all in the same string.

I tried using concat and group_concate and it didn't work so well... need help
sample.JPG

Norie

How did you try GROUP_CONCATENTATE and how did it not work?

tf2012

ASKER

I did try GROUP_CONCAT(notes.note_text) in the select statement and this was returned:
Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'the_db.quotes.create_date'; this is incompatible with sql_mode=only_full_group_by 0.000 sec

Norie

That was with the query you posted?

tf2012

ASKER

Here is the version with group_concat that failed:

select
	DATE(quotes.create_date) as create_date,
	quotes.year,
    quotes.make,
    quotes.model,
    quotes.type,
    quotes.city_org,
    quotes.prov_org,
    quotes.city_dest,
    quotes.prov_dest,
	quotes.quote_amount,
	quotes.origin_shipping_type,
    quotes.dest_shipping_type,
    quotes.order_note,
    GROUP_CONCAT(notes.note_text)
from notes
join quotes on notes.quote_no=quotes.quote_no
where 
(
	quotes.quote_type = "EN"
	OR notes.note_text like "%non runner%"
	OR notes.note_text like "%nr%"
    )
AND notes.note_text like "(by:%"
AND notes.note_text not like "(by: Roger) T%"
AND notes.note_text not like "(by: Doug) T%"
AND notes.note_text not like "(by: Larry) T%"
AND notes.note_text not like "%Quote Source%"
AND DATE(quotes.create_date) >= '2017-01-01'

Open in new window

ASKER CERTIFIED SOLUTION

theGhost_k8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob

You're getting an error as you need to group the other columns in the query. Note that you will still get multiple rows if the create_date is different between notes. It will join the notes together for those on the same date. Is that what you expected? If it suits your project you could group by month, year, week etc?

select
	DATE(quotes.create_date) as create_date,
	quotes.year,
    quotes.make,
    quotes.model,
    quotes.type,
    quotes.city_org,
    quotes.prov_org,
    quotes.city_dest,
    quotes.prov_dest,
	quotes.quote_amount,
	quotes.origin_shipping_type,
    quotes.dest_shipping_type,
    quotes.order_note,
    GROUP_CONCAT(notes.note_text)
from notes
join quotes on notes.quote_no=quotes.quote_no
where 
(
	quotes.quote_type = "EN"
	OR notes.note_text like "%non runner%"
	OR notes.note_text like "%nr%"
    )
AND notes.note_text like "(by:%"
AND notes.note_text not like "(by: Roger) T%"
AND notes.note_text not like "(by: Doug) T%"
AND notes.note_text not like "(by: Larry) T%"
AND notes.note_text not like "%Quote Source%"
AND DATE(quotes.create_date) >= '2017-01-01'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13

Open in new window