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

Question

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

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I have 3 tables and I need to list all the options along with all the posts that have or do not have options, I have actually done it, but it is showing me only the posts that have options but those that do not have options do not show them to me the listing.

The example in sqlfiddle: link

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

Tables:

table [post] - Save all the main posts

table [post_options] - Save all post options

table [post_has_options] - Save all posts 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

I appreciate your help.

mysql

asked by Learning and sharing 28.06.2018 в 20:10

source

2 answers

0

With a left join you can get all the posts that have or do not have options, but here it would be a touch to use two left.

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

Try that query.

answered by 28.06.2018 в 20:37

DataTable component of primefaces is not updated correctly Problem when counting with decimals on Android

score 2 · Accepted Answer

If I did not get it wrong, what you need to do is do this:

SELECT P.*,
       PO.*,
       PHO.*
       FROM post P 
       CROSS JOIN post_options PO
       LEFT JOIN post_has_options PHO
          ON PHO.id_post_work_type = PO.id
          AND PHO.Id_post = P.Id
       ORDER BY P.Id, PO.Id;

We show each post with four rows per post_options using a CROSS JOIN or Cartesian product, finally we add the additional information of post_has_options (if it exists) for each post.id and post_options.id

Result :

| id | id_post_type |   title |            create_at |          modified_at | id |        name |            create_at |          modified_at | id_post | id_post_work_type |            create_at |          modified_at |
|----|--------------|---------|----------------------|----------------------|----|-------------|----------------------|----------------------|---------|-------------------|----------------------|----------------------|
|  1 |            1 | Title 1 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  1 | Work type 1 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |       1 |                 1 | 2018-01-27T22:00:51Z | 2018-01-27T22:00:51Z |
|  1 |            1 | Title 1 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  2 | Work type 2 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |       1 |                 2 | 2018-01-27T22:00:54Z | 2018-01-27T22:00:54Z |
|  1 |            1 | Title 1 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  3 | Work type 3 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |       1 |                 3 | 2018-01-27T22:00:58Z | 2018-01-27T22:00:58Z |
|  1 |            1 | Title 1 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  4 | Work type 4 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  2 |            1 | Title 2 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  1 | Work type 1 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  2 |            1 | Title 2 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  2 | Work type 2 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |       2 |                 2 | 2018-01-27T22:45:19Z | 2018-01-27T22:45:19Z |
|  2 |            1 | Title 2 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  3 | Work type 3 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  2 |            1 | Title 2 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  4 | Work type 4 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  3 |            1 | Title 3 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  1 | Work type 1 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  3 |            1 | Title 3 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  2 | Work type 2 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  3 |            1 | Title 3 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  3 | Work type 3 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  3 |            1 | Title 3 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  4 | Work type 4 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  4 |            1 | Title 4 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  1 | Work type 1 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  4 |            1 | Title 4 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  2 | Work type 2 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  4 |            1 | Title 4 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  3 | Work type 3 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |
|  4 |            1 | Title 4 | 2018-01-27T14:58:24Z | 2018-01-27T23:10:00Z |  4 | Work type 4 | 2018-01-27T14:59:30Z | 2018-01-27T14:59:30Z |  (null) |            (null) |               (null) |               (null) |