1

I am trying to write two strings from selecting value from a column on multiple rows. I am getting a string back but the result is not correct, the string that is returned has repeated the specialism_name and qualification_name is being repeated as many times as there are qualifications for the worker. What do I need to do to adjust this SQL statement to make it write the names just once. I was thinking DISTINCT may work.

string_agg(specialism_name, ', ' ORDER BY  sp.specialism_name ASC) as worker_specialisms, 
string_agg(qualification_name, ', ' order by q.qualification_name asc) as worker_qualifications

from worker w
inner join users u using (worker_id)
inner join occupation o using (occupation_id)
inner join worker_specialism ws using (worker_id)
inner join specialism sp on ws.specialism_id = sp.specialism_id 
inner join worker_qualification wq using (worker_id)
inner join qualification q on wq.qualification_id = q.qualification_id

where 1 = 1 GROUP BY w.worker_id, u.user_id, o.occupation_id ORDER BY first_name ASC
ynwebdev
  • 11
  • 4
  • 1
    Are you using Postgres? You can use `string_agg(distinct ...)` –  Nov 08 '19 at 14:34
  • 1
    Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Nov 08 '19 at 14:34

0 Answers0