Search code examples
mysqljoininner-joinno-duplicates

mysql join three tables not duplicate value

I have the following tables

--subscribers--

id
name
e-mail

--categories--

id
subscriber_id
cat1
cat2
... so on

--messages--

id
title
message
cat1
another field
...

My query needs to return title, message and subscriber mails. Every subscriber can get many messages.

My query returns duplicate e-mails. I would like to get:

e-mail1 - title1, message1
e-mail2 - title1, message1, title2, message2 so on
e-mail3 - title1, message1, title2, message2, title3, message3

Now I have:

e-mail1 - title1, message1
e-mail2 - title1, message1
e-mail2 - title2, message2
e-mail3 - title1, message1
e-mail3 - title2, message2

How to dela with it? Thanks for any help.

Solution

  • Use GROUP BY email to get the unique email ids.

    Use GROUP_CONCAT(title), GROUP_CONCAT(message) to fetch all messages