CODEWITHSUNDEEP
mysql
Learning and sharing
Learning and sharing

Reputation: 1388

MySQL get all the records of related tables from many to many

I have three tables and I need to list all the options along with all the post that have or do not have options, I have actually done it, but I am only showing the posts that have options but those that do not have options do not show them in the list.

The example in SQL Fiddle: http://sqlfiddle.com/#!9/8d27dd/1

If you look at the list, the post with ID 1, has 3 options assigned to it and shows me a fourth row value null, and the post with ID 2, has an option assigned, but I need to show all the other rows with values null of all the existing options in the table[post_options].

Tables:

tabla[ post ] - Save all the main post

tabla[ post_options] - Save all the options of the post

tabla[ post_has_options] - Save all the post that have options

Code of tables:

CREATE TABLE `post` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_post_type` tinyint(3) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `FK_title_UNIQUE` (`title`) USING BTREE,
  KEY `FK_post_post_types` (`id_post_type`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `post` VALUES ('1', '1', 'Title 1', '2018-01-27 14:58:24', '2018-01-27 23:10:00');
INSERT INTO `post` VALUES ('2', '1', 'Title 2', '2018-01-27 14:58:24', '2018-01-27 23:10:00');
INSERT INTO `post` VALUES ('3', '1', 'Title 3', '2018-01-27 14:58:24', '2018-01-27 23:10:00');
INSERT INTO `post` VALUES ('4', '1', 'Title 4', '2018-01-27 14:58:24', '2018-01-27 23:10:00');

CREATE TABLE `post_has_options` (
  `id_post` int(10) unsigned NOT NULL,
  `id_post_work_type` tinyint(3) unsigned NOT NULL,
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_post`,`id_post_work_type`),
  KEY `id_post_work_type` (`id_post_work_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `post_has_options` VALUES ('1', '1', '2018-01-27 22:00:51', '2018-01-27 22:00:51');
INSERT INTO `post_has_options` VALUES ('1', '2', '2018-01-27 22:00:54', '2018-01-27 22:00:54');
INSERT INTO `post_has_options` VALUES ('1', '3', '2018-01-27 22:00:58', '2018-01-27 22:00:58');
INSERT INTO `post_has_options` VALUES ('2', '2', '2018-01-27 22:45:19', '2018-01-27 22:45:19');

CREATE TABLE `post_options` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `FK_name_UNIQUE` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `post_options` VALUES ('1', 'Work type 1', '2018-01-27 14:59:30', '2018-01-27 14:59:30');
INSERT INTO `post_options` VALUES ('2', 'Work type 2', '2018-01-27 14:59:30', '2018-01-27 14:59:30');
INSERT INTO `post_options` VALUES ('3', 'Work type 3', '2018-01-27 14:59:30', '2018-01-27 14:59:30');
INSERT INTO `post_options` VALUES ('4', 'Work type 4', '2018-01-27 14:59:30', '2018-01-27 14:59:30');

Query:

SELECT *
FROM post_options P
LEFT JOIN post_has_options PHO ON PHO.id_post_work_type = P.id

Upvotes: 2

Views: 39

Answers (1)

SeanW333
SeanW333

Reputation: 489

I think this is what you want:

SELECT 
    *
FROM 
    post AS p
LEFT OUTER JOIN 
    post_has_options AS pho
    ON 
    p.id = pho.id_post
LEFT OUTER JOIN
    post_options AS po
    ON
    po.id = pho.id_post_work_type

If not, let me know what you need different and I'll edit my answer. If this is what you need, please remember to mark as answered, so people will know you no longer need help.

Upvotes: 1

Related Questions