CODEWITHSUNDEEP
sqlsql-server
Vladyslav Ovcharenko
Vladyslav Ovcharenko

Reputation: 115

Count joined table

I have two tables tblFuneralHomes and tblFuneralTransactions. One table may has many transactions. I need to return user's funeralHomes and count transactions, but if funeralHomes has not transactions, it's not returned in result.

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

This is the result without join enter image description here

I know what current user has 3 funeralHomes.

But when I join transactions and count that, one of those homes hasn't transactions and it not displayed.

enter image description here

Upvotes: 3

Views: 65

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You have to use a LEFT JOIN instead of a INNER JOIN so all records of tblFuneralHomes are taken:

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

You are using columns of tblFuneralTransactions on the WHERE conditions. In case a record of tblFuneralHomes has no tblFuneralTransactions all column values of the (not available) tblFuneralTransactions record are NULL. So the following condition is false: tft.canceled = 0 AND tft.completed = 0.

To include all records of tblFuneralHomes you need to allow the columns of tblFuneralTransactions to be NULL. So you have to replace this condition

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

to the following

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

Upvotes: 3

Michał Turczyn
Michał Turczyn

Reputation: 37347

I'd suggest following query (little modification of your query):

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

I switched to left join, so not matched funeral homes will still be included in resultset. More over, I handled correpsonding nul values from other tables: when it's null then column should be 0, else 1. Then it's enough to sum those values.

Upvotes: 2

Related Questions