CODEWITHSUNDEEP
mysqljoin
threedollarbill
threedollarbill

Reputation: 165

duplicate values when joining multiple tables

I am trying to join 3 tables to generate a page that displays the member's name, their websites, and their music albums.

The problem I am having is that I am getting duplicate data.

For example, the rows I get in return repeat the album titles twice; and the website values are repeated thrice.

Below is how my tables look like:

member_profiles

id   |   alias_name
2    |  Lazy Turtle
3    |    Ms. Cat

member_websites

id   |   member_id   |         site_url         |   site_title
57   |       3       |     http://example.com   |   My Site
58   |       3       |   http://fakesite.com    |   Cool Site
59   |       2       |  http://anotherfake.com  |  Turtlez Site

music_albums

id   |   member_id   |        album_title
5    |       3       |      Cats Rock N Roll
6    |       2       |        My Slowness
7    |       3       |  Meow Pt. II (The Remixes)
8    |       3       |  The Worst Songs I've Made

How can I remedy this without having to separate my queries and possibly compromising the performance? My sql query looks something like this:

SELECT mp.name, mw.site_url, mw.site_title, ma.album_title
FROM member_profiles mp
LEFT JOIN member_websites mw ON mw.member_id = mp.id
LEFT JOIN music_albums ma ON ma.member_id = mp.id
WHERE mp.id = 3

Upvotes: 0

Views: 195

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

Just add some GROUP_COCAT().

SELECT mp.name, GROUP_CONCAT(mw.site_url), GROUP_CONCAT(mw.site_title), GROUP_CONCAT(ma.album_title)
FROM member_profiles mp
LEFT JOIN member_websites mw ON mw.member_id = mp.id
LEFT JOIN music_albums ma ON ma.member_id = mp.id
WHERE mp.id = 3

Upvotes: 1

Mukul Medatwal
Mukul Medatwal

Reputation: 170

You are using join with main table and other table has multiple websites and multiple music albums, you can use sub queries for you answer.

SELECT mp.name,
 ( select group_concat(mw.site_url) from member_websites mw where mw.member_id = mp.id) as websites ,
 (select group_concat(ma.album_title) from music_albums ma where ma.member_id = mp.id) as albums
FROM member_profiles mp
WHERE mp.id = 3

Upvotes: 0

Related Questions