3

In my SQL CE database I have three tables: customer, list and customerlist (a junction table between customer and list - as it is a many-to-many relationship).

I am trying to run a query that will display all current lists with also the number of customers who are currently subscribed to that list (counting from customerlist table).

Here is my current query:

select list.listid, count(customerlist.customerid) as numppl, list.ShortDesc
from list inner join customerlist on list.listid=customerlist.listid
group by list.ShortDesc, list.listid
order by numppl desc

The current structure of this database is:

[Customer]           [List]             [CustomerList]
CustomerId           ListId             CustomerListId
Name                 ShortDesc          CustomerId
Other details                           ListId

This currently returns all the lists who have customers currently assigned to them - but not lists which are empty. Empty lists are hidden.

I would like to modify this query to also display empty lists but I am struggling. My desired output is:

Name      numppl
listA     375
listB     45
listC     0

(In the example above, listC is currently not being returned).

Any thoughts on how to also show listC in the query?

Mike Baxter
  • 6,868
  • 17
  • 67
  • 115

2 Answers2

2

Use LEFT JOIN instead with ISNULL to replace NULL with 0:

SELECT 
  list.listid, 
  ISNULL(count(customerlist.customerid), 0) AS numppl, 
  list.ShortDesc
FROM list 
LEFT JOIN customerlist ON list.listid = customerlist.listid
GROUP BY list.ShortDesc, 
         list.listid
ORDER BY numppl DESC;

SQL Fiddle Demo

Update

For SQL Server CE, try this:

SELECT 
  list.listid, 
  SUM(CASE WHEN customerlist.customerid IS NULL THEN 0 ELSE 1 END) AS numppl, 
  list.ShortDesc
FROM list 
LEFT JOIN customerlist ON list.listid = customerlist.listid
GROUP BY list.ShortDesc, 
         list.listid
ORDER BY numppl DESC;
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • This SQL is displaying "numppl" as 'FALSE' for every row, like Iswanto San's answer. Any idea how I can get numppl to display as a count? – Mike Baxter Feb 20 '13 at 10:09
  • Ok so I tried it in SQL Fiddle and it works. But please remember this is SQL CE and SQL Fiddle will not reflect it accurately. – Mike Baxter Feb 20 '13 at 10:15
  • 1
    @Teifi - OK, Sorry. You are right. I forgot that. So what is the problem you got in SQL CE. Please show me the error you got. – Mahmoud Gamal Feb 20 '13 at 10:16
  • I am not getting any errors - the query works fine, just returns a boolean value when I want an int value :) Just found this: http://stackoverflow.com/questions/5890970/sql-server-compact-edition-isnullsth-returns-a-boolean-value I am reading it now, it is news to me :) – Mike Baxter Feb 20 '13 at 10:16
  • Thank you Mahmoud, that works great! I should have made it clearer I was using SQL CE :) – Mike Baxter Feb 20 '13 at 10:22
  • 1
    @Teifi You're welcome any time. No, it was clear that you are using SQL-CE, but it was my fault :( Thanks I just learned something new today. – Mahmoud Gamal Feb 20 '13 at 10:23
0

You must use LEFT JOIN syntax.

select list.listid,  case when count(customerlist.customerid) is null then 0 else count(customerlist.customerid) end as numppl, list.ShortDesc
from list left join customerlist on list.listid=customerlist.listid
group by list.ShortDesc, list.listid
order by numppl desc

Or you can use RIGHT JOIN :

select list.listid, case when count(customerlist.customerid) is null then 0 else count(customerlist.customerid) end as numppl, list.ShortDesc
from customerlist right join list on customerlist.listid=list.listid
group by list.ShortDesc, list.listid
order by numppl desc

Use COALESCE : 

select list.listid,  coalesce(count(customerlist.customerid),0) as numppl, list.ShortDesc
from list left join customerlist on list.listid=customerlist.listid
group by list.ShortDesc, list.listid
order by numppl desc
Iswanto San
  • 18,263
  • 13
  • 58
  • 79