Подсчитать объединенную таблицу

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть две таблицы tblFuneralHomes и tblFuneralTransactions . В одной таблице может быть много транзакций. Мне нужно вернуть транзакции пользователя funeralHomes и подсчитать, но если у funeralHomes нет транзакций, это не возвращается в результате.

 Select tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key, count(tft.id) as counts from tblFuneralHomes tfh
inner join tblFuneralTransactions tft on tft.funeral_home_guid = tfh.funeral_home_guid where (tft.canceled=0 and tft.completed=0)
and tfh.funeral_home_guid in (select funeral_home_guid 
from tblDirectorHomes
where director_id = '1789a3ae-95e5-4719-ac09-bd1ada01dd5b') 
group by tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key

Это результат без объединения

Я знаю, у какого текущего пользователя 3 funeralHomes .

Но когда я присоединяюсь к транзакциям и подсчитываю это, в одном из этих домов нет транзакций, и он не отображается.

Ответ №1:

Вы должны использовать a LEFT JOIN вместо INNER JOIN , чтобы были взяты все записи tblFuneralHomes :

 SELECT tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key, COUNT(tft.id) AS counts 
FROM tblFuneralHomes tfh LEFT JOIN tblFuneralTransactions tft ON tft.funeral_home_guid = tfh.funeral_home_guid 
WHERE (tft.funeral_home_guid IS NULL OR (tft.canceled = 0 AND tft.completed = 0))
    AND tfh.funeral_home_guid IN (
        SELECT funeral_home_guid 
        FROM tblDirectorHomes
        WHERE director_id = '1789a3ae-95e5-4719-ac09-bd1ada01dd5b'
    )
GROUP BY tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key

Вы используете столбцы tblFuneralTransactions на WHERE условиях. В случае, если запись tblFuneralHomes не имеет tblFuneralTransactions всех значений столбца в (недоступной) tblFuneralTransactions записи, являются NULL . Итак, следующее условие равно false: tft.canceled = 0 AND tft.completed = 0 .

Чтобы включить все записи tblFuneralHomes , вам нужно разрешить, чтобы столбцы tblFuneralTransactions были NULL . Итак, вы должны заменить это условие

 (tft.canceled = 0 AND tft.completed = 0)

к следующему

 (tft.funeral_home_guid IS NULL OR (tft.canceled = 0 AND tft.completed = 0))

1. Я пробовал ЛЕВОЕ СОЕДИНЕНИЕ и ЛЕВОЕ ВНЕШНЕЕ соединение, но получил тот же результат

2. смотрите обновление (добавленную tft.funeral_home_guid IS NULL часть). Попробуйте весь запрос еще раз.

3. Я не знаю почему, но все еще получил не все дома. Если я удалю where (tft.завершено = 0 и tft.отменено = 0) Я получаю все дома с подсчетами.

4. вам нужно разрешить значения NULL — смотрите Объяснение

5. Выполнено. Спасибо!

Ответ №2:

Я бы предложил следующий запрос (небольшая модификация вашего запроса):

 Select tfh.funeral_home_guid, 
       tfh.funeral_home_name, 
       tfh.reference_key, 
       sum(case when tft.id is null then 0 else 1 end) as counts 
from tblFuneralHomes tfh
left join tblFuneralTransactions tft on tft.funeral_home_guid = tfh.funeral_home_guid 
where (tft.canceled=0 and tft.completed=0)
  and tfh.funeral_home_guid in (select funeral_home_guid 
                                from tblDirectorHomes
                                where director_id = '1789a3ae-95e5-4719-ac09-bd1ada01dd5b') 
group by tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key

Я переключился на left join , поэтому не совпадающие похоронные бюро все равно будут включены в результирующий набор. Более того, я обработал сопоставление nul значений из других таблиц: если значение равно null, тогда столбец должен быть 0, иначе 1. Затем достаточно суммировать эти значения.