Help needed making a tidy join query

(Simplfied for question asking purposes)
I have a help desk system with support categories and assignable
employees. I would like to run a query that will pull back a list of
all categories along with a count of unassigned tickets. So my tables
are:
tblSupportCategories
CategoryID int
CategoryName varchar(50)
tblSupportTickets
TicketID int
CategoryID int
SupportContact varchar(15) (default is null)
if I say
select tsc.categoryid,count(*)
from tblsupportcategories tsc
left outer join tblsupporttickets tst on tsc.categoryid =
tst.categoryid and tst.supportcontact is null
what I get is a list of all the support categories along with a non
zero number whereas what I want is something that looks like
Website 0
Office 2
E-Mail 1
Warranty 3
I suspect that an elegant query can be written to query this back in
one statement but I am at a loss as to how to do it. If anyone could
help, I certainly appreciate it.
TIAAssuming you really are using SQL Server (layout smells like Access) you cou
ld
do the following:
Select TSC.CategoryId, Count([TST.TicketId])
From tblSupportCategories As TSC
Left Join tblSupportTickets As TST
On TSC.CategoryId = TST.CategoryId
And TST.SupportContact Is Null
I'm assuming that TicketId is the PK of the tickets table (you didn't provid
e
DDL so I can't tell for sure).
Note that the criteria is in the Join clause not the Where clause. In this w
ay,
the tickets list will be filtered *before* it is joined to the categories ta
ble.
Also, by using Count([TST.TicketId]), I'm counting all ticket values where
support contact was null.
Thomas
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:50tl511ophieh5k8v1i1haiv7ls6ee4gi7@.
4ax.com...
> (Simplfied for question asking purposes)
> I have a help desk system with support categories and assignable
> employees. I would like to run a query that will pull back a list of
> all categories along with a count of unassigned tickets. So my tables
> are:
> tblSupportCategories
> CategoryID int
> CategoryName varchar(50)
> tblSupportTickets
> TicketID int
> CategoryID int
> SupportContact varchar(15) (default is null)
> if I say
> select tsc.categoryid,count(*)
> from tblsupportcategories tsc
> left outer join tblsupporttickets tst on tsc.categoryid =
> tst.categoryid and tst.supportcontact is null
>
> what I get is a list of all the support categories along with a non
> zero number whereas what I want is something that looks like
> Website 0
> Office 2
> E-Mail 1
> Warranty 3
> I suspect that an elegant query can be written to query this back in
> one statement but I am at a loss as to how to do it. If anyone could
> help, I certainly appreciate it.
> TIA
>|||Matthew Speed wrote:
> (Simplfied for question asking purposes)
> I have a help desk system with support categories and assignable
> employees. I would like to run a query that will pull back a list of
> all categories along with a count of unassigned tickets. So my tables
> are:
> tblSupportCategories
> CategoryID int
> CategoryName varchar(50)
> tblSupportTickets
> TicketID int
> CategoryID int
> SupportContact varchar(15) (default is null)
> if I say
> select tsc.categoryid,count(*)
> from tblsupportcategories tsc
> left outer join tblsupporttickets tst on tsc.categoryid =
> tst.categoryid and tst.supportcontact is null
>
> what I get is a list of all the support categories along with a non
> zero number whereas what I want is something that looks like
You do? That's not even a legal query ... you left out the Group By clause

> Website 0
> Office 2
> E-Mail 1
> Warranty 3
>
Sample data would have been helpful ... However, this should get you what
you want:
select 1 categoryid, 'Website' categoryname into #categories
union all
select 2,'Office'
union all
select 3,'Email'
union all
select 4,'Warranty'
select 1 ticketid,1 categoryid,'test1' supportcontact into #tickets
union all
select 2,2,'test'
union all
select 3,2,null
union all
select 4,3,null
union all
select 5,4,null
union all
select 6,4,null
union all
select 7,4,null
union all
select 8,2,null
select tsc.categoryid,coalesce(ticketcount,0)
unassignedtickets
from #categories tsc
left outer join (
select categoryid,count(*) ticketcount from
#tickets
where supportcontact is null
group by categoryid
) tst on tsc.categoryid =
tst.categoryid
drop table #categories
drop table #tickets
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Mon, 11 Apr 2005 18:53:22 -0400, "Bob Barrows [MVP]"
<reb01501@.NOyahoo.SPAMcom> wrote:

>Matthew Speed wrote:
>You do? That's not even a legal query ... you left out the Group By clause
>
That is not my actual query. I was trying to include a simplified
version of my real query and left out the group by clause in my
rewrite.|||
>Assuming you really are using SQL Server (layout smells like Access)
I have a SQL 7.0 MCDBA that I am currently upgrading to SQL 2K, thank
you very much. I haven't used Access to query anything since Access
97.

>Select TSC.CategoryId, Count([TST.TicketId])
>From tblSupportCategories As TSC
> Left Join tblSupportTickets As TST
> On TSC.CategoryId = TST.CategoryId
> And TST.SupportContact Is Null
>Note that the criteria is in the Join clause not the Where clause. In this
way,
>the tickets list will be filtered *before* it is joined to the categories table.[/c
olor]
I had this part.
>Also, by using Count([TST.TicketId]), I'm counting all ticket values where
>support contact was null.
>
This is what got me. I was trying to count(*). My original thinking
was that by doing a left outer join with the null clause in the join I
would get back a zero unless rows were matched on the right side of
the join. Unfortunately, the outer also got me a one count on every
left side row that didn't match on the right. Obviously that was
wrong but your solution worked great.
Thanks