Optimise these 3 queries into one?

I have these 3 queries - they are the same except each fetches record counts
for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
nImages (type 2). Is there any way I could get all 3 of these (based on the
Node.Type integer) with a single query?

IF @.Error = 0
BEGIN
SELECT @.nSubsets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 0

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nAssets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 1

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nImages = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 2

SET @.Error = @.ERROR
ENDSELECT
@.nSubsets = COUNT(CASE Node.type WHEN 0 THEN 1 END),
@.nAssets = COUNT(CASE Node.type WHEN 1 THEN 1 END),
@.nImages = COUNT(CASE Node.type WHEN 2 THEN 1 END)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

--
David Portas
----
Please reply only to the newsgroup
--|||Robin,

I'm assuming you wanted the result in one row. This is untested, but
you get the idea...

IF @.Error = 0
BEGIN
SELECT SUM (CASE Node.Type
WHEN 0 THEN 1
ELSE 0
END) AS nSubSets_cnt
,SUM (CASE Node.Type
WHEN 1 THEN 1
ELSE 0
END) AS nAssets_cnt
,SUM (CASE Node.Type
WHEN 2 THEN 1
ELSE 0
END) AS nImages_cnt
FROM Node
INNER JOIN
Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND Node.Type = in (0, 1, 2)

SET @.Error = @.ERROR
END

Christian.|||How about this?

SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
@.nAssets = sum(case when node.type = 1 then 1 else 0 end),
@.nImages = sum(case when node.type = 2 then 1 else 0 end),
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

I think tyhis will get you what you are looking for. I haven't run it
against anything, but I'm fairly sure it's good to go.

Hope it works for you,
Carl

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> I have these 3 queries - they are the same except each fetches record counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
> nImages (type 2). Is there any way I could get all 3 of these (based on the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||Use the CASE function.
EG:

select

CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 0 then 1
else 0 end "NT=0",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 1 then 1
else 0 end "NT=1",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 2 then 1
else 0 end "NT=2",

from ...

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bs9jhc$gfo$1$8300dec7@.news.demon.co.uk...
> I have these 3 queries - they are the same except each fetches record
counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> nImages (type 2). Is there any way I could get all 3 of these (based on
the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||All good stuff. Thanks for all 3 replys.

"Carl Reeds" <creeds0001@.msn.com> wrote in message
news:956eb1fa.0312231411.4e7cec31@.posting.google.c om...
> How about this?
> SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
> @.nAssets = sum(case when node.type = 1 then 1 else 0 end),
> @.nImages = sum(case when node.type = 2 then 1 else 0 end),
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> I think tyhis will get you what you are looking for. I haven't run it
> against anything, but I'm fairly sure it's good to go.
> Hope it works for you,
> Carl
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> > I have these 3 queries - they are the same except each fetches record
counts
> > for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> > nImages (type 2). Is there any way I could get all 3 of these (based on
the
> > Node.Type integer) with a single query?
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nSubsets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 0
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nAssets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 1
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nImages = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 2
> > SET @.Error = @.ERROR
> > END