CODEWITHSUNDEEP
mysqldatabase
Bipul Roy
Bipul Roy

Reputation: 566

Remove duplication of rows while joining multiple tables - Mysql

media

id  | title   | ...
1   | a song  |
2   | a video |

media setting

media_id  | setting_id  | chosen_option
  1       |    1        |    2
  1       |    2        |    3
  2       |    1        |    1
  2       |    2        |    4

So I have media table with various infromation about user uploaded media files and they have two settings 1.privacy( option-1 for public and option-2 for private) and 2.age-safty( option-3 is for all and option-4 is for adult only). Now when a user(adult) searching for a media, suppose with a title starts with a.....

Here is my query:

SELECT 
m.id AS media_id, m.title AS media_title, 
ms.setting_id AS setting, ms.chosen_option as opt 
FROM media m 
LEFT JOIN media_setting ms ON m.id = ms.media_id
WHERE m.title LIKE 'a%'

AND It will give me an output with duplicate rows one row with each setting which I don't want.

So what i want is :

media_id  |  media_title  | setting_1  | option_for_1 |  setting_2 | option_for_2

1         |  a song       |   1        |    2         |    2        |     3
2         |  a video      |   1        |    1         |    2        |     4

How can i achieve this? Thanks.

Upvotes: 0

Views: 33

Answers (1)

Strawberry
Strawberry

Reputation: 33945

As per comments, I'd stick with the query you've got, and resolve the display issues in application code.

But anyway, here's a standard (and non-dynamic) approach in sql...

CREATE TABLE media
(id SERIAL PRIMARY KEY
,title VARCHAR(20) NOT NULL
);

INSERT INTO media VALUES
(1,'a song'),
(2,'a video');

DROP TABLE IF EXISTS media_setting;

CREATE TABLE media_setting
(media_id INT NOT NULL
,setting_id INT NOT NULL
,chosen_option INT NOT NULL
,PRIMARY KEY(media_id,setting_id)
);

INSERT INTO media_setting VALUES
(1,1,2),
(1,2,3),
(2,1,1),
(2,2,4);

SELECT m.*
     , MAX(CASE WHEN s.setting_id = 1 THEN chosen_option END) option_for_1
     , MAX(CASE WHEN s.setting_id = 2 THEN chosen_option END) option_for_2
  FROM media m
  LEFT
  JOIN media_setting s 
    ON s.media_id = m.id
 GROUP
    BY m.id;

+----+---------+--------------+--------------+
| id | title   | option_for_1 | option_for_2 |
+----+---------+--------------+--------------+
|  1 | a song  |            2 |            3 |
|  2 | a video |            1 |            4 |
+----+---------+--------------+--------------+

Upvotes: 1

Related Questions