Remove duplication of rows while joining multiple tables - Mysql

Question

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.

Also, note that option is a reserved word, so I don't think this is your query — Strawberry
– Strawberry, Commented Mar 13, 2019 at 14:08
I just replicated my situation . No the actual query @Strawberry — Bipul Roy
– Bipul Roy, Commented Mar 13, 2019 at 14:09
If it was me, I'd stick with the query you've got, and resolve the display issues in application code. Although, that said, I wonder if the value for setting_1 can ever be anything other than '1', and likewise for setting_2 — Strawberry
– Strawberry, Commented Mar 13, 2019 at 14:13
value for the settings are fixed setting_1=1 and setting_2=2 @Strawberry — Bipul Roy
– Bipul Roy, Commented Mar 13, 2019 at 14:19

Strawberry · Accepted Answer · 2019-03-13 14:19:41Z

1

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 |
+----+---------+--------------+--------------+

answered Mar 13, 2019 at 14:19

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bipul Roy Over a year ago

Thanks for the answer @Strawberry.

Bipul Roy Over a year ago

Everything went right except i had a part sum(num_of_like) in my select query which doubled the actual value. I would like to know if you have solution @Strawberry

Strawberry Over a year ago

Don't have that bit. The column doesn't appear in the data set, anyway.

Collectives™ on Stack Overflow

Remove duplication of rows while joining multiple tables - Mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related