SQL, создание поля в ответе, если есть совпадение по одной из двух таблиц, как?

Question

[Денис Николас]

Есть три таблицы.
Одна с текущим списком машин, вторая с чёрным списком и третья с белым.
В каждой из них указан номер машины(приведён к единому формату, разумеется).
Как составить запрос к бд, что бы помимо всех полей из текущего журнала, добавлялось поле `list`, в которое, при условии совпадения номеров(скажем, поле `numbers`) из списков с номерами из журнала, добавлялось название журнала?

journal - текущий
id - numbers
0 - А 111 АА
1 - В 222 ВВ
2 - С 333 СС

blacklist - чёрный список
id - numbers
0 - A 111 AA
1 - E 444 EE

whitelist - белый
id - numbers
0 - C 333 CC
1 - H 555 HH

Желаемый ответ
id - numbers - list
0 - А 111 АА - blacklist
1 - В 222 ВВ - null
2 - С 333 СС - whitelist

Возможно ли вообще это сделать одним запросом? Очень не хочется перебирать по массивам каждого списка.

Answer 1

[Alex]

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

Не обрабатывается ситуация когда есть в whitelist и blacklist одновременно! Если таковое возможно, надо переписать.

Comments

[Денис Николас]

Спасибо, довольно таки просто, пошёл "курить" оператор CASE.

[Alex]

case X when 1 then 1 when 2 then 2 end
тоже самое что
if x = 1 { 1 }
else if x=2 { 2 }
...

[Денис Николас]

Alex: Вообще - не возможно, моделирую ситуации. На всякий случай сделаю так:
CASE
WHEN black.id IS NOT NULL THEN 'black'
WHEN white.id IS NOT NULL THEN 'rentor'
WHEN white.id IS NOT NULL AND black.id IS NOT NULL THEN 'double'
ELSE NULL END AS list

Всё верно?

[Alex]

Нет, сработает 1е условие и до 3его не дойдет....
переместите 3е на место 1го

[Денис Николас]

Alex: Понял, спасибо. Слишком привик, к if, if else, else.

[Alex]

Денис Николас:
CASE
WHEN black.id IS NOT NULL AND white.id IS NULL THEN 'black'
WHEN white.id IS NOT NULL AND black.id IS NULL THEN 'rentor'
WHEN white.id IS NOT NULL AND black.id IS NOT NULL THEN 'double'
ELSE NULL END AS list

как вариант в том же порядке

[Денис Николас]

Alex: Да это уже понял. Либо первый срабатывает, либо полностью описывать.

Answer 2

[Дмитрий Еремин]

Обязательно разве выводить под это дело отдельные таблицы?
Можно же создать таблицу
id - numbers - isInBlackList - isInWhiteList
0 - А 111 АА - true - false
1 - В 222 ВВ - false - false
2 - С 333 СС - false - true

И работать с этим. Если машина в списке - true. Не в списке - false

Comments

[Денис Николас]

Обязательно, в таблицах помимо номера и id ещё много всякого, тут же написал, для абстракции.

[Alex]

Денис Николас: ну если не в одну таблицу, то объединить Black и White было бы логично ( я думаю, что у них поля совпадают на 90%) но тут дело вкуса и нормальных форм бойса-кодда. =)

[Денис Николас]

Alex: Таки различаются сильно.
В белом листе прописываются дополнительные параметры: какого типа абонемент, организация, телефон, имя, статус доставки смс, а в чёрном - просто номер и описание причины занесения.