Correcting logical errors in a view

[galorin]

I use the following view to tell me how many HouseTypes (HT) have been checked as ready, and what quote they belong to. h.ht_id is the Housetype ID that identfies all the details regarding the entry. The whole HTCheck table works as follows

Housetype design ready for check > entry made into HTCheck
Housetype checked and is ready > update table, set column Checked_By = name of person signing off.

select
h.quote_id AS quote
,count(h.ht_id) AS qcount
,count(c.HT_ID) AS ccount
from HouseType as h
left join HTCheck as c on h.ht_id = c.HT_ID
group by h.quote_id

The logic error is not checking for the existence of the row in HTCheck, but rather if it exists and Checked_By is not null. I have tried

select
h.quote_id AS quote
,count(h.ht_id) AS qcount
,count(c.HT_ID) AS ccount
from HouseType as h
left join HTCheck as c on h.ht_id = c.HT_ID
where c.Checked_By is not null
group by h.quote_id

but the results of this query only tells me the number of quotes that are signed off, not a total of those that are and are not checked. Been pulling my hair out over this one.

 

[r937]

change the word WHERE to AND



r937.com | rudy.ca