SQL, creating a field in the response if there is a match on one of the two tables, how?

Question

PHP

There are three tables.
One with the current list of machines, the second with a black list and the third with a white one.
Each of them indicates the number of the car (brought to a single format, of course).
How to make a query to the database so that, in addition to all fields from the current journal, the `list` field is added, to which, if the numbers (say, the `numbers` field) from the lists with the numbers from the journal match, the name of the journal is added?
journal - current
id - numbers
0 - A 111 AA
1 - B 222 BB
2 - C 333 CC

blacklist - blacklist
id - numbers
0 - A 111 AA
1 - E 444 EE

whitelist - white
id - numbers
0 - C 333 CC
1 - H 555 HH

Desired answer
id - numbers - list
0 - A 111 AA - blacklist
1 - B 222 BB - null
2 ​​- C 333 SS - whitelist
Is it even possible to do this with one request? I really don't want to iterate over the arrays of each list.

Answer 1

select t1.id,t1.number, case when t2.id is not null then 'blacklist' when t3.id is not null then 'whitelist' else '-' end as list
from journal t1
left join blacklist t2 on t1.number = t2.number
left join whitelist t3  on t1.number = t3.number

The situation is not processed when it is in the whitelist and blacklist at the same time! If possible, it should be rewritten.

Answer 2

Is it necessary to display separate tables for this matter?
You can also create a table
id - numbers - isInBlackList - isInWhiteList
0 - A 111 AA - true - false
1 - B 222 BB - false - false
2 - C 333 CC - false - true
And work with it. If the machine is in the list - true. Not in the list - false