Trouble displaying 0 for null values

Question

I am having trouble returning a value of 0 in my results when the answer is null, my current code is as follows.

select distinct bt2.isbn, bt2.btname, Count ('bcid')

from 
booktitle bt1, booktitle bt2,
bookcopy bc1, bookcopy bc2, 
loan l1, loan l2

where
bt1.isbn = bc1.isbn
and bc1.bcid = l1.bcid
and l1.bcid = l2.bcid
and l2.bcid = bc2.bcid
and bc2.isbn = bt2.isbn


group by  bt2.isbn, bt2.btname

order by Count ('bcid') desc;

How can I get the resulting answers to display a value of 0 where they are currently not returning the row at all due to a null value. I have tried using previously answered questions to help me but can't seem to grasp the concept. any and all help will be much appreciated

---

Using all the help kindly offered so far i have come up with this code

select bt1.isbn, bt1.btname, Count (bc.bcid)    


from booktitle bt1
  inner join bookcopy bc
   on bc.isbn = bt1.isbn
  inner join loan l
   on l.bcid = bc.bcid

  left outer join Bookcopy bc2
   on bc2.isbn = bt1.isbn

 group by  bt1.isbn, bt1.btname
 order by Count (2bc.bcid) desc;

However it still seems to only return rows that have a count greater then 0, is there a way to make it return rows where the count = 0 as well as rows where the count is greater?

Answer 1

A query based on inner joins will only produce rows when data exists. If, for example, there are rows in "bt2" that have no matching joins in "bt1", then you will not get those bt2 rows in your final set.

To produce a set with (say) all your bt2s even if there are no matching bt1s, you'd need to use outer joins. Revising your code to use ON clauses (all but mandatory, if you're going to work with outer joins), you'd switch

from booktitle bt2
 inner join bookcopy bc2
  on bc2.isbn = bt2.isbn
 inner join loan l2
  on l2.bcid = bc2.bcid
 inner join loan l1
  on l1.bcid = l2.bcid
 inner join bookcopy bc1
  on bc1.bcid = l1.bcid
 inner join Booktitle bt1
  on bt1.isbn = bc1.isbn

to

from booktitle bt2
 inner join bookcopy bc2
  on bc2.isbn = bt2.isbn
 inner join loan l2
  on l2.bcid = bc2.bcid
 inner join loan l1
  on l1.bcid = l2.bcid
 inner join bookcopy bc1
  on bc1.bcid = l1.bcid
 LEFT OUTER JOIN Booktitle bt1
  on bt1.isbn = bc1.isbn

This would produce rows with all your bt2 data (assuming all those intervening inner joins worked out), with no data--represented by NULLs--in the bt1 result set. Cap it off by counting on a column from the bt1 set, and you should get your zeroes:

SELECT distinct bt2.isbn, bt2.btname, Count (bt1.isbn)    
 from booktitle bt2
  inner join bookcopy bc2
   on bc2.isbn = bt2.isbn
  inner join loan l2
   on l2.bcid = bc2.bcid
  inner join loan l1
   on l1.bcid = l2.bcid
  inner join bookcopy bc1
   on bc1.bcid = l1.bcid
  left outer join Booktitle bt1
   on bt1.isbn = bc1.isbn
 group by  bt2.isbn, bt2.btname
 order by Count (bt1.isbn) desc

Answer 2

Refactoring your original query to use proper join syntax gives me this:

select bt2.isbn   ,
       bt2.btname ,
       Count ('bcid')
from booktitle bt1 ,
join bookcopy  bc1 on bc1.isbn = bc1.isbn 
join loan      l1  on l1.bcid  = bc1.bcid
join loan      l2  on l2.bcid  = l1.bcid
join bookcopy  bc2 on bc2.bcid = l2.bcid
join booktitle bt2 on bt2.bcid = bc2.bcid
group by  bt2.isbn, bt2.btname
order by Count ('bcid') desc

Which still doesn't make clear exactly what you're trying to accomplish. However, if we extend your query to use left joins across the board, thus

select *
from      booktitle bt1
left join bookcopy  bc1 on bc1.isbn = bc1.isbn 
left join loan      l1  on l1.bcid  = bc1.bcid
left join loan      l2  on l2.bcid  = l1.bcid
left join bookcopy  bc2 on bc2.bcid = l2.bcid
left join booktitle bt2 on bt2.bcid = bc2.bcid

you'll get at least 1 row for every entry in booktitle: any join that doesn't match will have all its columns set to null, which will propagate down the join chain as (if your SQL engine is standards compliant) null fails all tests save explicits tests for nullity via is [not] null. That gives us our candidate set, that we need to summarize. Rows that matched the entire join chain will have non-null columns for bt2; those columns will contain all null values for rows that didn't didn't match the entire join chain.

You want a count of how many entries in bt1 matched the entire join chain, broken out by isbn/btname. To do that we add in the grouping columns we need and the aggregate functions we need. The following will, I believe, do the trick, based on my understanding (such as it is) of the original query:

select bt1.isbn   ,
       bt1.btname ,
       sum( case when bt2.bcid is not null then 1 else 0 end )
from      booktitle bt1
left join bookcopy  bc1 on bc1.isbn = bc1.isbn 
left join loan      l1  on l1.bcid  = bc1.bcid
left join loan      l2  on l2.bcid  = l1.bcid
left join bookcopy  bc2 on bc2.bcid = l2.bcid
left join booktitle bt2 on bt2.bcid = bc2.bcid
group by  bt1.isbn, bt1.btname
order by 3 desc ,
         1 ,
         2

You'll notice the sum(case...end), where the case expression acts as a discriminant function yielding a 0/1 value that can be counted; the same result would be achieved via a simple count(tb2.bcid) as null values are excluded from consideration by aggregate functions, with the sole exception of count(*). I prefer to use the sum( case ... end ) technique as it makes the intent clear: anybody whose understanding of SQL is not up to snuff will find the results less...surprising.

Hope this helps.