MySQL - Remove duplicate records entity relationship many to many

Question

As I can get records from multiple tables omitting duplicate, for example I have this table "code attached tables":

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for interests
-- ----------------------------
DROP TABLE IF EXISTS `interests`;
CREATE TABLE `interests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of interests
-- ----------------------------
INSERT INTO `interests` VALUES ('1', 'Sport');
INSERT INTO `interests` VALUES ('2', 'Technology');
INSERT INTO `interests` VALUES ('3', 'Games');
INSERT INTO `interests` VALUES ('4', 'Security');
INSERT INTO `interests` VALUES ('5', 'Movies');

-- ----------------------------
-- Table structure for interests_has_user
-- ----------------------------
DROP TABLE IF EXISTS `interests_has_user`;
CREATE TABLE `interests_has_user` (
  `interests_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`interests_id`,`user_id`),
  KEY `fk_interests_has_user_user1_idx` (`user_id`),
  KEY `fk_interests_has_user_interests_idx` (`interests_id`),
  CONSTRAINT `fk_interests_has_user_interests` FOREIGN KEY (`interests_id`) REFERENCES `interests` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_interests_has_user_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of interests_has_user
-- ----------------------------
INSERT INTO `interests_has_user` VALUES ('1', '1');
INSERT INTO `interests_has_user` VALUES ('2', '1');
INSERT INTO `interests_has_user` VALUES ('3', '1');
INSERT INTO `interests_has_user` VALUES ('4', '1');
INSERT INTO `interests_has_user` VALUES ('5', '1');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `country` varchar(45) DEFAULT NULL,
  `state` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'Name', 'email@email.com', '123123', '1', '1', '1');

I need to get records from the table[ user ] and the table[ interests_has_user ], where a user can have many interests at the table table[ interests_has_user ], I'm doing the query this way:

SELECT 
    user.id,
    user.name,
    user.email,
    user.country,
    user.state,
    user.city,
    interests_has_user.interests_id,
    interests_has_user.user_id
FROM 
    user
LEFT JOIN interests_has_user
ON user.id = interests_has_user.user_id
WHERE user.id = 1;

And I throw all records of the table table[ interests_has_user ], but in all rows the user is repeated, image attached with the result.

Note: What is shaded in yellow should be empty or null fields.

What the best solution for this, use INNER JOIN, or separate consutas.

I appreciate your help, thank you very much.

Answer 1

INNER JOIN will not return users without interests. So it is not what you want. If you want to lighten-up the query results you could do two queries:

First find out the user details

SELECT 
    user.id,
    user.name,
    user.email,
    user.country,
    user.state,
    user.city
FROM 
    user
WHERE user.id = 1

Then the interests ids.

SELECT user.id, interests_has_user.interests_id, interests_has_user.user_id 
FROM user 
LEFT JOIN interests_has_user ON user.id = interests_has_user.user_id 
WHERE user.id = 1