CODEWITHSUNDEEP
mysqlsqlselectinner-join
user164863
user164863

Reputation: 641

MySQL query with several values in one result

I need to get a list of rows with one query where one of the column consists of several values and I couln't figure this out.

I have three tables that logs mailings to people. One table has all the contact data of a person such as first_name, last_name, address etc. Second table consist of list of mailing names with its unique IDs. Like #1 - Mailing_1, #2 Mailing_2 etc. The third table liaise those two by logging mailing id and people id. Now I need to get the full list of people where the last column would show list of mailins each people got.

Here is what I have tried:

SELECT p.fname, p.lname, p.address m.mailing_name FROM people p
JOIN mailings_liaison l ON l.contact_id - p.id
JOIN mailings m ON m.id = l.mailings_id
WHERE 1
ORDER by p.lname ASC

I get what I need by this but if a person had two or more mailings it shows up as additional rows. I would need to unite those rows so each person has only one row in the query result with several mailings listed in the last column, i.e. I get:

| 1. | John | White | [email protected] | Mailing_1 |
| 2. | John | White | [email protected] | Mailing_2 |

But somehow instead I want to get:

| 1. | Jhon | White | [email protected] | Mailing_1 Mailing_2 |

Is this possible?

Upvotes: 0

Views: 123

Answers (1)

John Woo
John Woo

Reputation: 263683

use GROUP_CONCAT

SELECT p.fname, p.lname, p.address, 
       GROUP_CONCAT(m.mailing_name SEPARATOR ' ') 
FROM   people p
       JOIN mailings_liaison l ON l.contact_id - p.id
       JOIN mailings m ON m.id = l.mailings_id
GROUP  BY p.fname, p.lname, p.address
ORDER  by p.lname ASC

Upvotes: 4

Related Questions