Левое соединение не возвращает ожидаемые результаты

#sql-server #tsql

Вопрос:

У меня есть таблица поставщиков (поставщиков):

  ----- ------------- -- 
| ID  |   Vendor    |  |
 ----- ------------- -- 
|   1 | ABC Company |  |
|   2 | DEF Company |  |
|   3 | GHI Company |  |
| ... | ...         |  |
 ----- ------------- --

и таблица служб (AllServices):

  ----- ------------ -- 
| ID  |  Service   |  |
 ----- ------------ -- 
|   1 | Automotive |  |
|   2 | Medical    |  |
|   3 | Financial  |  |
| ... | ...        |  |
 ----- ------------ --

и таблица, которая связывает два (VendorServices):

  ----------- ----------- 
| Vendor ID | ServiceID |
 ----------- ----------- 
|         1 |         1 |
|         1 |         3 |
|         3 |         2 |
|       ... |       ... |
 ----------- -----------

Обратите внимание, что одна компания может предоставлять несколько служб, в то время как некоторые компании могут не предоставлять ни одной из перечисленных служб.

Результаты запроса, которые я хочу, будут для данного поставщика:

  ------------ ---------- 
| Service ID | Provided |
 ------------ ---------- 
|          1 |        0 |
|          2 |        0 |
|          3 |        1 |
|        ... |      ... |
 ------------ ----------

Где перечислены ВСЕ службы и те, которые предоставляет данный поставщик, будут иметь 1 в предоставленном столбце, в противном случае ноль.

Вот что у меня пока есть:

 SELECT
    VendorServices.ServiceID,
    <Some Function> AS Provided 
FROM
    AllServices LEFT JOIN VendorServices ON AllServices.ID = VendorServices.ServiceID
WHERE
    VendorServices.VendorID = @VendorID
ORDER BY
    Service

У меня есть два неизвестных:

Приведенный выше запрос возвращает не каждую запись в таблице AllServices; и
Я не знаю, как написать функцию для предварительно выбранного столбца.

1.Выполнение a LEFT JOIN для таблицы, а затем ссылка на эту таблицу в WHERE без учета NULL значений неявно преобразует JOIN в an INNER JOIN . Я подозреваю, что предложение в WHERE должно быть в ON . В противном случае вам нужно обрабатывать NULL значения, так как *nothing` равно NULL , в том числе NULL .

Ответ №1:

Для получения столбца требуется соединение по ЛЕВОМУ краю с AllServices to VendorServices и case выражение provided :

 select s.id,
       case when v.serviceid is null then 0 else 1 end provided
from AllServices s left join VendorServices v
on v.serviceid = s.id and v.vendorid = @VendorID

Посмотрите демонстрацию.

1. Это работает, но я немного новичок в SQL, поэтому, пожалуйста, объясните, что это за часть v.vendorid = @VendorID является частью условия соединения, а не в предложении WHERE .

2. @SezMe если бы условие v.vendorid = @VendorID было в предложении WHERE, то все несовпадающие строки AllServices были бы отфильтрованы, потому что возвращались бы только ненулевые идентификаторы поставщиков, поэтому это соединение фактически было бы ВНУТРЕННИМ соединением. Если поместить условие в предложение ON, результатом будут все строки AllServices.