MYSQL Объединяет три таблицы с исключениями

#mysql #exception #join #datatables

#mysql #исключение #Присоединиться #таблицы данных

Вопрос:

Я работаю с тремя таблицами SQL и использую INNER JOIN для объединения этих таблиц. Вот обзор того, как выглядят таблицы.

 CREATE TABLE `User` (
   `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `firstName` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `lastName` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `email` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)                                                                       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


CREATE TABLE `Ownership` ( 
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `user` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
   `certificate` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `unicity` (`user`,`certificate`)                                              ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

CREATE TABLE `Certificate` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `domain` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `creationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   
   `expirationDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   `type` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`)                                                                       ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Запрос на объединение таблиц следующий :

 select user, lastName, domain 
from Ownership 
    INNER JOIN User ON Ownership.user = User.id 
    INNER JOIN Certificate ON Ownership.certificate = Certificate.id;`

У меня есть такой тип результата, например :

Что я хотел бы, так это получить только одного пользователя для одного доменного имени и создать исключение для выполнения объединения, только если значение certificate.domain еще не существует.

Возможно ли это с MYSQL?

Спасибо

1. Не публикуйте структуры таблиц в виде изображений. Мы не можем копировать / вставлять для выполнения тестов.

2. Можете ли вы предоставить пример результата, который вы хотите получить?

3. @Cid, я обновил структуры таблиц

4. @PavelSmirnov Я хотел бы получить тот же результат с удалением первой или третьей строки, неважно, какой именно.

Ответ №1:

Вам нужен предварительный запрос, чтобы, по крайней мере, определить некоторый компонент уникальности, связанный с данным доменом. Чтобы понять это, давайте рассмотрим это.

 select 
      C.domain,
      min( O.user ) JustOneUser 
   from 
      Certificate C
         JOIN Ownership O
            on C.ID = O.Certificate
   group by
      C.Domain

Таким образом, это приведет к получению одного пользователя для данного домена. Теперь используйте ЭТОТ результат, чтобы связать пользователя с доменом.

 select
      PQ.Domain,
      U.LastName,
      U.FirstName
   from
      (select 
             C.domain,
             min( O.user ) JustOneUser 
          from 
             Certificate C
                JOIN Ownership O
                   on C.ID = O.Certificate
          group by
             C.Domain ) PQ
      JOIN User U
         on PQ.JustOneUser = U.ID