Объединить два внешних ключа из одной таблицы в SQL

Question

Не совсем уверен, как задать этот вопрос, поэтому, если кто-то хочет отредактировать, чтобы лучше сформулировать, пожалуйста. Однако я хочу присоединиться к пользовательской таблице, однако в строке есть два FK из пользовательской таблицы.

item_tbl
id | ownerId | lastModifiedById | itemName
------------------------------------------
1  |       1 |                2 | "Blog Post"

user_tbl
id | username
-------------
1  |     John
2  |    Sally

Желаемый вывод (или что-то подобное)

Owner Username | last modified by |       item
----------------------------------------------
          John |            Sally | "Blog Post"

В настоящее время я делаю два запроса, чтобы получить эту информацию. Есть ли лучший (читай: более эффективный) способ?

Answer 1

SELECT user_tbl.username Owner, a.username Modifier, item_tbl.itemName 
FROM item_tbl 
JOIN user_tbl 
ON item_tbl.ownerId = user_tbl.id 
JOIN user_tbl a 
ON item_tbl.lastModifiedById = a.id;

работал для любопытных, на что намекал Дрю в комментариях

Answer 2

схема

create table user_tbl
(   id int auto_increment primary key,
    username varchar(50) not null
);

create table item_tbl
(   id int auto_increment primary key,
    ownerId int not null,
    lastModifiedById int not null,
    itemName varchar(50) not null,
    CONSTRAINT fk_own_user FOREIGN KEY (ownerId) REFERENCES user_tbl(id),
    CONSTRAINT fk_mod_user FOREIGN KEY (lastModifiedById) REFERENCES user_tbl(id)
);
insert user_tbl(username) values('John');   -- this becomes id 1
insert user_tbl(username) values('Sally');  -- this becomes id 2

Быстрый тест на отказ FK:

insert item_tbl(ownerId,lastModifiedById,itemName) values (9,9,'blah');

Код ошибки: 1452. Невозможно добавить или обновить дочернюю строку: ограничение внешнего ключа не выполнено

Не удалось, как и ожидалось, это хорошо, потому что данные были плохими

Успех:

insert item_tbl(ownerId,lastModifiedById,itemName) values (1,2,'the Blog Post is this');

---

запрос

select u1.username,u2.username,i.itemName 
from item_tbl i 
join user_tbl u1 
on u1.id=i.ownerId 
join user_tbl u2 
on u2.id=i.lastModifiedById;
+----------+----------+-----------------------+
| username | username | itemName              |
+----------+----------+-----------------------+
| John     | Sally    | the Blog Post is this |
+----------+----------+-----------------------+

Всегда загружайте ограничения внешнего ключа для обеспечения ссылочной целостности. Признак хорошо спроектированной схемы - это не что иное, как случайность и мусор.

Страница руководства по ограничениям внешнего ключа.

Все, чего не хватает, так это рассмотрения ключей (индексов), добавленных в item_tbl для ownerId а также lastModifiedById столбцы, чтобы сделать соединения очень быстрыми и избежать сканирования таблицы