SQL как сделать выборку по двум таблицам одним запросом?

Question

[Денис Самофалов]

Есть две таблицы:

account
id, Name, Number, Date
account_history
id, Name, Number, Date, ParentId, Status

В таблице account хранятся актуальные данные, в таблице account_history история изменений данных таблицы account.

Когда мы изменяем строку в таблице account в таблице account_history создается запись с предыдущими данными изменяемой строки и в поле Status попадает значение 'active'

Когда мы удаляем строку из таблицы account в таблице account_history также создается запись с данными удаленной строки и в поле Status попадает значение 'deleted'

Поле account_history.ParentId - думаю понятно что там

Так вот, нужно сделать выборку по двум таблицам, одним запросом:
выбрать последние 50 записей с сортировкой по
account.id DESC и account_history.ParentId DESC
и чтоб из второй таблицы в выборку попадали только те записи у которых Status=deleted

---------------------------------------------------------------------------------------------------

допустим мы создали запись в таблице account с именем Паша(id1), потом создали еще одну с именем Лена(id2), и еще с именем Вася(id3), и допустим удалили Лена, в истории создалась запись Лена(id1) и теперь мы делаем выборку

результат:
id--Name--ParentId--Status
1---Паша
1---Лена---2 --------- deleted
3---Вася

Вот такой порядок выборки должен быть
Паша и Вася у нас выбирается из account, а Лена из account_history.

поля ParentId, Status есть только у таблицы account_history

Comments

[V Sh.]

Почитайте про JOIN, ORDER BY, WHERE и LIMIT. Думаю, что для решения должно хватить.

[Akina]

Поле account_history.ParentId - думаю понятно что там

Нет. У меня есть как минимум два (хотя на самом деле - два по три) разных варианта.

[Slava Rozhnev]

Опубликуйте структуру таблиц и тестовые данные на SQLize.online

Answer 1

[Антон Шаманов]

JOIN'ом

Comments

[Денис Самофалов]

Это не совсем то что я хотел услышать, но я пробовал не получаеться, возможно вы подскажите как?

[ThunderCat]

Денис Самофалов, как пробовали? Где код того что вы пробовали? Написать такой запрос не сложно, важно чтобы в процессе вы что-то делали, хотя бы пробовали... Иначе вопрос попадает под категорию "задания" (ака "сделайте за меня"), которые запрещены правилами сервиса.

[Денис Самофалов]

ThunderCat,

поверьте я уже битый час пробую
вот последний вариант, но он выдает только одну последнюю запись из истории и дублирует ее 10 раз

SELECT *
FROM
	account t0
	account_history t1
WHERE
	t1.StatusId='deleted'
ORDER BY 
	t0.id,
	t1.ParentId
DESC LIMIT 0, 10

[Антон Шаманов]

Денис Самофалов, где тут join?

[Денис Самофалов]

Антон Шаманов, я не знаю как использовать join в данной ситуации т.к. строки из двух таблиц никак не связанны, по крайней мере те которые мне нужно достать, строки которые выбираются из таблицы account_history имеют стату deleted и связи через ParentId уже не имеют т.к. удаленны из таблицы account.
Может я не до конца понимаю join, как я понимаю он используется для объединения данных из разных таблиц если их что то связывает

[Антон Шаманов]

Денис Самофалов, fk это вообще другая песня. теперь кажется понял: в данной ситуации подойдет union

[Денис Самофалов]

Антон Шаманов,

неправильно вам написал
"Может я не до конца понимаю join, как я понимаю он используется для объединения данных из разных таблиц если их что то связывает"

Я пробовал UNION у меня не получилось, но я не работал с UNION, возможно через с помощью UNION есть решение

[Антон Шаманов]

Денис Самофалов, освойте теорию перед тем как заниматься практикой

[Денис Самофалов]

спасибо за совет, но я не за этим ответам сюда пришел

[Антон Шаманов]

Денис Самофалов, вы пришли за готовым решением

вопрос попадает под категорию "задания" (ака "сделайте за меня"), которые запрещены правилами сервиса.

[Денис Самофалов]

ThunderCat, Я тебя понял, к чему ты, но мне действительна нужна помощь в этом вопросе. На его решение я потратил около 6 часов, но не смог решить. Тебе обязательно нужны пруфы?

[Антон Шаманов]

Денис Самофалов, твои запросы нужны чтобы подсказать что в них не так. я не знаю как можно налажать с юнионом
Если база mysql, то сортируешь после объединения

(SELECT id, name, `date`  FROM t1)
UNION
(SELECT id, name, `date`  FROM t2)
ORDER BY `date` LIMIT 10

если нет, сортируешь по отдельности

[Денис Самофалов]

Антон Шаманов, По вашему примеру у меня получилось следующее:

(SELECT id, name, `date`  FROM t1)
UNION
(SELECT id, name, `date`  FROM t2)
ORDER BY `date` LIMIT 10

Данный пример работает корректно, но не так как необходимо, т.к. в выборке мне нужны поля которые есть только в t2. У меня получилось следующее

(SELECT id, name, 'tmpvalue' as uniq_for_t2, `date`  FROM t1)
UNION
(SELECT id, name, uniq_for_t2, `date`  FROM t2 WHERE Status='del')
ORDER BY `date` 
LIMIT 10

У меня получилось частично решить задачу.
Получилось выбрать из двух таблиц нужные строки и сделать из t2 выборку уникального поля
Методом проб я добавил в запрос эту строку - "'tmpvalue' as uniqForT2", т.к. без нее запрос возвращает ошибку

В таблице t2 есть ParentId и вот при сортировке нужно чтоб ParentId был как id для t1. Мне сложно объяснить это, вот пример

id--Name--ParentId--Status
1---Паша
4---Лена---2
3---Вася

1,2,3

[Денис Самофалов]

Но Pa@SilenceOfWinter, Но поле ParentId есть только в t2 и если я после объединения пытаюсь сортировать по данному полю, возвращает ошибку

[Антон Шаманов]

Денис Самофалов, откуда взялось uniq_for_* ?!
SELECT id, Name, id AS ParentId FROM account

[Денис Самофалов]

Получилось

(SELECT id, Name, '' AS Status, id AS q FROM account)
UNION
(SELECT id, Name, Status, ParentId AS q FROM account_history WHERE Status='deleted')
ORDER BY q DESC 
LIMIT 0, 10

Только один момент меня смущает, у таблице account_history есть поле Status, у таблице account такого поля нет, и мне приходится дописывать эту строку "'' AS Status", как то без нее можно?

Answer 2

[microbot]

Если правильно понял ТЗ, то нужно получить удаленные аккаунты. join может дать нам эту фильтрацию, сделав соединение.

select *
from account
join account_history on account_history.parentId = account.id and account_history.Status = 'deleted'
order by account.id desc
limit 50

Если нужно выбрать аккаунты и с тем условием, что не у всех может быть статус удален, то так:

select *
from account
left join account_history on account_history.parentId = account.id and account_history.Status = 'deleted'
order by account.id desc, account_history.parentId desc
limit 50

Comments

[Денис Самофалов]

К сожалению результат выборки пустой в обоих случаях

[microbot]

Денис Самофалов, а parentId указывает на id записи из account?

[Денис Самофалов]

да

[microbot]

Денис Самофалов, какое количество выведет такой запрос:

select count(*) from account_history where parentId in(select id from account) and Status = 'deleted'

[Денис Самофалов]

допустим мы создали запись в таблице account с именем Паша(id1), потом создали еще одну с именем Лена(id2), и еще с именем Вася(id3), и допустим удалили Лена, в истории создалась запись Лена(id1) и теперь мы делаем выборку

результат:
id--Name--ParentId--Status
1---Паша
1---Лена---2 --------- deleted
3---Вася

Вот такой порядок выборки должен быть
Паша и Вася у нас выбирается из account, а Лена из account_history.

поля ParentId, Status есть только у таблицы account_history

[Денис Самофалов]

microbot,
select count(*) from account_history where parentId in(select id from account) and Status = 'deleted'

этот вариант тоже не работает

[microbot]

Денис Самофалов,

select
account.Name, account_history.parentId, account_history.State
from account
left join account_history on account_history.parentId = account.id
order by account.id desc

Если этот не работает, то подскажи, к какой базе запрос посылаешь? И, выводятся ли какие-то ошибки.

[Денис Самофалов]

microbot,
не сработал, тут не указанно что нужно выбирать только удаленные записи
работаю с базой Mysql если я вас правильно понял

[microbot]

Давайте определимся, что значит "не сработало". Выдает ошибку или запрос отрабатывает, но выдает 0 записей?

[Денис Самофалов]

запрос отрабатывает и выдает неверный результат

результат: первая запись с пустыми полями последующие из истории по порядку все подряд