CODEWITHSUNDEEP
mysqlsql
Miguel Stevens
Miguel Stevens

Reputation: 9230

Combining 2 Sql results rows

I have a query that get's a list of information from the answers table, it checks if the specific coach that is viewing the page has records with his coach_id in the answers table, and if other coaches have answers.

Now when there's more than 1 coach that has answers for 1 specific employee (werknemer_id) as you can see in the screenshot below, the field get's duplicated for that coach.

enter image description here

I'm wondering if it's possible to combine the 2 fields into 1 field..%aybe with comma seperated values? So i don't have a duplicate name in my result?

This is my code

SELECT l.id, l.naam, r.id AS revisie, r.beschrijving, w.id AS werknemer, w.voornaam, w.achternaam, a.coach_id,
CASE WHEN a.coach_id = 3 THEN 1 ELSE 0 END AS zelf_ingevuld,
CASE WHEN a.coach_id != 3 AND a.coach_id != 3 THEN (SELECT CONCAT(voornaam, ' ', achternaam) FROM coaches WHERE id = a.coach_id ) ELSE 0 END AS coach_ingevuld
FROM lijsten l
INNER JOIN revisies r ON l.id = r.lijst_id
INNER JOIN werknemerlijsten wl ON wl.lijst_id = l.id
INNER JOIN werknemers w ON w.id = wl.werknemer_id
INNER JOIN klanten k ON k.id = w.klant_id
LEFT JOIN antwoorden a ON w.id = a.werknemer_id AND r.id=a.revisie_id
LEFT JOIN coaches c ON c.id = a.coach_id
WHERE r.actief = 1
GROUP BY r.id, c.id, w.id

Thank you!

Upvotes: 0

Views: 47

Answers (1)

GeorgeLambadas
GeorgeLambadas

Reputation: 151

use SELECT CONCAT(table1.column_name, ',', table2.column_name) AS new_column_alias ...

this should give you the output you'll need in a comma separated field that you can name as you wish

Upvotes: 1

Related Questions