Optimise these 3 queries into one?

[Robin Tucker]

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

[David Portas]

SELECT
@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
--

[Christian Maslen]

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.

[Carl Reeds]

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" <id*************************@reallyidont.com> wrote in message news:<bs*******************@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

[mountain man]

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" <id*************************@reallyidont.com> wrote in
message news:bs*******************@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

[Robin Tucker]

All good stuff. Thanks for all 3 replys.

"Carl Reeds" <cr********@msn.com> wrote in message
news:95**************************@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" <id*************************@reallyidont.com> wrote in

message news:<bs*******************@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