Как подставить в вывод SQL запроса значения из другой таблицы?

Question

[Юрий Чудновский]

Дано:
Таблица table1 со столбцами: ID, time, userID1, userID2
Таблица table2 со столбцами ID, username, ...
При запросе вида
SELECT time, userID1, userID2 FROM table1;
получаем ответ вида

1467822104 268 833
1467822659 9 18

Я выяснил как превратить в человеческий вид первое значение -
select FROM_UNIXTIME(time) AS formatted_date ...
и вывод теперь имеет вид

2016-07-06 19:21:44 268 833
2016-07-06 19:30:59 9 18

Но никак не соображу, как вот эти циферки превратить в имена, т.е. вместо значения userID1/userID2 вывести соответствующие им username?

Answer 1

[Дмитрий Энтелис]

SELECT
   FROM_UNIXTIME(time) AS formatted_date, 
   user1.username AS user1_username, 
   user2.username AS user2_username
FROM table1
LEFT JOIN table2 as user1 on user1.ID = table1.userID1
LEFT JOIN table2 as user2 on user2.UD = table1.userID2

Comments

[Юрий Чудновский]

Мне непонятно вот эта часть:
user1.username AS user1_username,
user2.username AS user2_username
ведь у меня есть только table2.username, никакого table1.username нет. Я чего-то не понимаю?

[WayMax]

Юрий Чудновский: для выборки тебе нужны 2 таблицы "table2" (одна чтобы получить userID1, а вторая - userID2), поэтому для таблицы "table2" задается 2 разных имени "user1" и "user1"

[WayMax]

WayMax: опечатка :)
поэтому для таблицы "table2" задается 2 разных имени "user1" и "user2"

[Юрий Чудновский]

Сергей: Попробовал, всё равно "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'user1 as user1 on user1.ID блаблабла"

[Дмитрий Энтелис]

Юрий Чудновский: покажите реальную структуру таблиц, мы сейчас в угадайку играем

[Юрий Чудновский]

Дмитрий Энтелис: Дам-с, пожалуй перейдём.
desc chars_deathlog;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Time | int(10) unsigned | NO | | NULL | |
| CharID | int(10) unsigned | NO | | NULL | |
| KillerID | int(10) unsigned | NO | | NULL | |
| IsKnockout | tinyint(3) unsigned | YES | | 0 | |
+------------+---------------------+------+-----+---------+----------------+
desc character;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'character' at line 1
desc lif_1.character;
+----------------------------------+----------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+----------------------+------+-----+-------------------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| IsActive | tinyint(3) unsigned | NO | MUL | 1 | |
| Name | varchar(9) | NO | UNI | NULL | |
| LastName | varchar(15) | NO | | NULL | |
| GeoID | int(10) unsigned | YES | | 0 | |
(и ещё куча полей которые в данном случае не нужны).
Как видите, к таблице character в отличии от первой почему-то нельзя обратиться без указания базы данных (use конечно же выполнен), видимо это и приводит к ошибке при правильном, в общем-то, синтаксисе

[Дмитрий Энтелис]

Юрий Чудновский: потому что character это служебное слово https://dev.mysql.com/doc/refman/5.7/en/keywords.html, не стоит так таблицу называть.
Пишите название таблицы в кавычках вот так`character`

[Юрий Чудновский]

Дмитрий Энтелис: Вот оно чё, Михалыч... Таблицу так не я называл :)

[Юрий Чудновский]

Дмитрий Энтелис: Итак, ПОЧТИ, думаю, готов правильный запрос... Меня смущает только "AS user1_username" - тут нет опечатки? Что это, алиас? Если алиас, то чего?

[Дмитрий Энтелис]

Юрий Чудновский: это просто для удобства чтения ответа от sql сервера.
Мы запрашиваем username дважды, нужно же их как то отличать :)

[Юрий Чудновский]

Дмитрий Энтелис: Ну тогда я не понимаю что сейчас не так.

SELECT FROM_UNIXTIME(`Time`) AS formatted_date, user1.`Name` AS user1_name, user2.`Name` AS user2_name FROM `chars_deathlog` LEFT JOIN `character` AS user1 on user1.ID = chars_deathlog.CharID LEFT JOIN `character` ASS user2.ID = chars_deathlog.KillerID;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'user2.ID = chars_deathlog.KillerID' at line 1

[Дмитрий Энтелис]

Юрий Чудновский: у Вас написано "ASS" вместо "AS"

[Юрий Чудновский]

Дмитрий Энтелис: упс, опечатался когда переводил служебные слова в upper case. Но ошибка, в общем-то, не в этом. Точнее, после исправления этой опечатки ошибок аж две, и возможно опять что-то связанное с конкретными наименованиями столбцов:

SELECT FROM_UNIXTIME(`Time`) AS formatted_date, user1.`Name` AS user1_name, user2.`Name` AS user2_name FROM `chars_deathlog` LEFT JOIN `character` AS user1 on user1.ID = chars_deathlog.CharID LEFT JOIN `character` ASS user2.ID = chars_deathlog.KillerID;SELECT FROM_UNIXTIME(`Time`) AS formatted_date, user1.`Name` AS user1_name, user2.`Name` AS user2_name FROM `chars_deathlog` LEFT JOIN `character` AS user1 on user1.ID = chars_deathlog.CharID LEFT JOIN `character` AS user2.ID = chars_deathlog.KillerID;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'user2.ID = chars_deathlog.KillerID' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.ID = chars_deathlog.KillerID' at line 1

Либо что-то не так с использованием алиасов.

[Дмитрий Энтелис]

Юрий Чудновский: Слушайте, я все понимаю, но Вам нужно реально внимательнее запросы делать.
SQL великолепно структурирован, по сути любой не сложный SQL запрос легко читаем простым человеческим языком.
Вот Вы пишите:

LEFT JOIN `character` AS user2.ID = chars_deathlog.KillerID;

ну это же не валидно от слова совсем. Нужно писать

LEFT JOIN `character` AS user2 ON user2.ID = chars_deathlog.KillerID;

И почему у Вас 2 sql запроса в одной конструкции? Зачем?

[Юрий Чудновский]

Дмитрий Энтелис: Хм, не заметил что два запроса, извините :) Видимо случайно скопипастил как-то вместо нужного. Спасибо большое за вашу помощь, это работает:
SELECT FROM_UNIXTIME(`Time`) AS formatted_date, user1.`Name` AS user1_name, user2.`Name` AS user2_name FROM `chars_deathlog` LEFT JOIN `character` AS user1 on user1.ID = chars_deathlog.CharID LEFT JOIN `character` AS user2 on user2.ID = chars_deathlog.KillerID;

Answer 2

[WayMax]

SELECT t2.username AS name1, t3.username AS name2
FROM table1, table2 t2, table2 t3
WHERE table1.userID1 = t2.id AND table1.userID2 = t3.id;

Правда это для MS SQL Server, MySQL под рукой сейчас нет.

UPD: Оказался вторым :) Вариант без "LEFT JOIN" работает так же, но компактнее и более читаемый :)

Comments

[Юрий Чудновский]

К сожалению и так не получается, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table2 t2, table2 t3 WHERE блаблабла"

[WayMax]

Юрий Чудновский: Так бы и писали что у вас "MariaDB", а то в тегах "MySQL" :) Чего там не нравится Машке я не знаю :) И мой вам совет - используйте нормальные СУБД :)

[Юрий Чудновский]

WayMax: Ну по идее у них синтаксис одинаковый, сервис так же называется и sql файлы взаимосовместимые.

[WayMax]

Юрий Чудновский: посмотрите еще здесь (для MySQL)
www.sql.ru/forum/769196/alias-tablicy
www.sql.ru/forum/673910/podskazhite-s-zaprosom

[Юрий Чудновский]

WayMax: Спасибо, но я начинаю подозревать что тут загвоздка у меня в непонятной особенности этого MariaDB, требующей местами указывать не только имя таблицы, но и имя базы, sic.

[Дмитрий Энтелис]

WayMax: FYI
Вариант с from table1, table2 на АБСОЛЮТНО любой sql базе будет работать омерзительно, т.к сначала будет строиться полное декартово произведение таблиц, и только затем фильтроваться.
Моя любимая задачка на собеседованиях)