CODEWITHSUNDEEP
mysqlsql
chase cabrera
chase cabrera

Reputation: 129

How to consolidate in a query?

I need some help writing a query to consolidate names into a list that are associated with a foreign key. Here's my current query,

select distinct concat(c_first, ' ', c_last) as name, pmt_no 
from disbursements d
left join contacts c on c.c_no = d.b_no
where d.ba_no = 1

My result set looks like this

Louis Vaz, 586014

Antionette An, 690682

Brian Cald, 690682

Mark Brian, 3233902

My desired outcome is

Louis Vaz, 586014

Antionette An - Brian Cald, 690682

Mark Brian, 3233902

Please note that both the people with pmt_no 690682 are now joined together with a '-' separating them.

Upvotes: 2

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270003

I think you want group_concat() along with a group_by:

select group_concat(c_first, ' ', c_last separator ' - ') as names,
       pmt_no 
from disbursements d left join
     contacts c 
     on c.c_no = d.b_no
where d.ba_no = 1
group by pmt_no;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521569

You can use the GROUP_CONCAT() function to achieve what you want:

select group_concat(distinct concat(c_first, ' ', c_last) SEPARATOR ' - ') as name, pmt_no 
from disbursements d
left join contacts c on c.c_no = d.b_no
where d.ba_no = 1
group by pmt_no

Upvotes: 3

Related Questions