Mysql also retrieves unlinked data from two tables

When I retrieve data from two tables like the one below, I would like to obtain category data that does not have any categories attached to the template. What should I do?
If possible, I would like to get it in one query.

○ Template tbl
id | Category id | Template Name | Delete Flag
1 | 1 | Template 1 | 0
2 | 1 | Template 2 | 0
3 | 2 | Template 3 | 0
4 | 2 | Template 3 | 1

Thank you for your cooperation.

mysql

2022-09-30 18:07

1 Answers

Create a table

CREATE TABLE `category`(
  `id`int(11) NOT NULL,
  `categor_name`varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `template`(
  `id`int(11) NOT NULL,
  `category_id`int(11) DEFAULT NULL,
  `template_name`varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert the data.

INSERT INTO `category`(`id`,`categor_name`)VALUES(1,'category 1');
INSERT INTO `category`(`id`,`categor_name`)VALUES(2,'category 2');
INSERT INTO `category`(`id`,`categor_name`)VALUES(3,'category 3');

INSERT INTO `template`(`id`, `category_id`, `template_name`)VALUES(1,1, 'Template 1');
INSERT INTO `template`(`id`, `category_id`, `template_name`)VALUES(2,1, 'Template 2');
INSERT INTO `template`(`id`, `category_id`, `template_name`)VALUES(3,2, 'Template 3');

The search statement is as follows.

SELECT 
    template.id,
    category.id,
    template_name
from
    category
        LEFT JOIN
    template ON category.id = template.category_id

LEFT JOINSee here

2022-09-30 18:07

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656