1
votes

I have following tables:

  • table1 id, user_id, first_name, last_name
  • table2 id, user_id, company_name, first_name, last_name, partner_id
  • table3 businesses id, business_name

I want to create query which will return rows of data: user_id, (first_name last_name), (business_name, business_name ...) When user is associated with multiple businesses, something like this: 123, John Doe, Business1, Business2

I can get kind of duplicate rows when I use following query:

SELECT table1.first_name, table1.last_name, table2.copmany_name,
       (case when concat(table1.first_name, table1.last_name) = '' then table2.company_name else concat_ws(' ', table1.first_name,table1.last_name) end) as name,
       table3.name as business_name
FROM table1
JOIN table2 ON table1.user_id = table2.user_id
JOIN table3 ON table2.partner_id = table3.id

Here is sample from that query:

123, John Doe, Business1
123, John Doe, Business2
125, Marie Bird, Business3

And I want to get:

123, John Doe, Business1, Business2
125, Marie Bird, Business3

Is that possible?

Thank you

2
To a file or just to the query results? If the former, check out stackoverflow.com/questions/1517635/…. If the latter, look up the concatenation operator ||Hambone
Just to get query results. My problem is how to concatenate those 2 or more rows related to the same user_idlradin
Aah, I see now... I didn't do a good job of reading your question.Hambone
Do you need to see it exactly the way you listed it (as pure CSV), or is notionally fine to have the actual output vary, provided you get multiple rows of data in a single row, the way you have outlined?Hambone

2 Answers

0
votes

You can use array_agg in postgresql.Aggregate functions compute a single result from a set of input values.:

SELECT table1.first_name, table1.last_name, table2.copmany_name,
       (case when concat(table1.first_name, table1.last_name) = '' then table2.company_name else concat_ws(' ', table1.first_name,table1.last_name) end) as name,
       array_agg(table3.name) as business_name
FROM table1
JOIN table2 ON table1.user_id = table2.user_id
JOIN table3 ON table2.partner_id = table3.id
0
votes

The string_agg aggregate function allows you to concatinate expression together:

SELECT   table1.first_name,
         table1.last_name,
         table2.copmany_name,
         (CASE WHEN CONCAT(table1.first_name, table1.last_name) = '' 
               THEN table2.company_name 
               ELSE CONCAT_WS(' ', table1.first_name, table1.last_name) 
          END) AS name,
         STRING_AGG(table3.name, ', ') AS business_name
FROM     table1
JOIN     table2 ON table1.user_id = table2.user_id
JOIN     table3 ON table2.partner_id = table3.id
GROUP BY table1.first_name,
         table1.last_name,
         table2.copmany_name,
         (CASE WHEN CONCAT(table1.first_name, table1.last_name) = '' 
               THEN table2.company_name 
               ELSE CONCAT_WS(' ', table1.first_name, table1.last_name) 
          END) AS name