SQL how to make a selection on two tables in one query?

Question

SQL

There are two tables:

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

The account table stores up-to-date data, the account_history table stores the history of data changes in the account table.

When we change a row in the account table, a record is created in the account_history table with the previous data of the changed row, and the Status field gets the value 'active'

When we delete a row from the account table, a record with the data of the deleted row is also created in the account_history table, and the Status field gets the value ' deleted'

Field account_history.ParentId - I think it's clear what's there

So, you need to make a selection on two tables, with one query:
select the last 50 records sorted by
account.id DESC and account_history.ParentId DESC
and so that only those records with Status=deleted get into the selection from the second table

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

let's say we created an entry in the account table with the name Pasha (id1), then we created another one with with the name Lena (id2), and also with the name Vasya (id3), and let's say they deleted Lena, a record Lena (id1) was created in the history and now we are sampling

the result:
id--Name--ParentId--Status
1---Pasha
1---Lena---2 --------- deleted
3---Vasya

This is the sampling order
Pasha and Vasya should be selected from account, and Lena from account_history.

ParentId, Status fields are available only for the account_history table

Answer 1

JOIN

Answer 2

If I understood the TK correctly, then you need to get remote accounts. join can give us this filtering by making a 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

If you need to select accounts and with the condition that not everyone can have the status deleted, then like this:

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