SQL - displaying zero results

Question

I am looking to display a 'O' or 'No results found' when no records are found based off my query but can't get it right. I think the problem comes from that I am trying to display information about the records I am counting along with how many there are that fit the criteria. Perhaps its not the count that I need to change with ISNULL but something to modify the result if no rows are outputted.

Thanks for any help in advance.

select t3.countyname, t2.titleunitname, t1.transdesc, count (isnull(t3.orderkey,0)) as c1
        from tblorder as t3
        left join qryOrderRecord1 as t1 on t3.OrderKey = t1.OrderKey
        left join qrytitleunits as t2 on t3.titleunitnum = t2.titleunitnum
        WHERE t1.transdesc = 'LETTERS'
        AND (t3.OrderDateTime between '7/7/2010' AND '7/7/2010')
        AND t3.countyname = 'Frankfurt'
        AND t2.titleunitname = 'Maxwell'
        group by t3.countyname,t1.transdesc, t2.titleunitname

Answer 1

I think you need to count t1.orderkey t3 is on the left side of the outer join so it is the t1.orderkey values that will be NULL.

Also I moved the Filters on t1 and t2 into the JOIN conditions not the WHERE clause

If that doesn't do the trick providing some sample data in your question will help.

SELECT t3.countyname,
    t2.titleunitname,
    t1.transdesc    ,
    COUNT(t1.orderkey) AS c1 /*As dotjoe's answer suggests*/
FROM tblorder                     AS t3
    LEFT JOIN qryOrderRecord1     AS t1
    ON  t3.OrderKey  = t1.OrderKey
    AND t1.transdesc = 'LETTERS'
    LEFT JOIN qrytitleunits AS t2
    ON  t3.titleunitnum  = t2.titleunitnum
    AND t2.titleunitname = 'Maxwell'
WHERE t3.OrderDateTime BETWEEN '7/7/2010' AND '7/7/2010' /*Seems odd! Should these be 
                                                           different dates?*/
AND t3.countyname = 'Frankfurt'
GROUP BY t3.countyname,
    t1.transdesc      ,
    t2.titleunitname

Answer 2

That count(isnull(column, 0)) is the same as count(1) or count(*). See this question for the why...

http://stackoverflow.com/questions/1221559/count-vs-count1

Maybe you want count(t1.orderkey)? This would not count any null t1.orderkey's.

Answer 3

Give this a shot.

select t3.countyname, t2.titleunitname, t1.transdesc, count (isnull(t3.orderkey,0)) as c1 
into #Tmp
from tblorder as t3 
    left join qryOrderRecord1 as t1 on t3.OrderKey = t1.OrderKey 
    left join qrytitleunits as t2 on t3.titleunitnum = t2.titleunitnum 
where t1.transdesc = 'LETTERS' 
  AND (t3.OrderDateTime between '7/7/2010' AND '7/7/2010') 
  AND t3.countyname = 'Frankfurt' 
  AND t2.titleunitname = 'Maxwell' 
group by t3.countyname,t1.transdesc, t2.titleunitname 

if @@ROWCOUNT > 0
    select *
    from #Tmp
else
    select 'No Results Found.'