using Count(*) with INNER JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using Count(*) with INNER JOIN

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-16 : 11:58:36

I am hoping to get a total of records so that I can perform some paging but I am having trouble getting a total count of the result from my inner join. here is my code:


Create Procedure [dbo].[prc_SelectProductsWithPaging]

@Brand varchar(30),
@Category  varchar(30),
@PageNumber int,
@PageSize int,
@NumberOfPages int output

AS

DECLARE @TotalRecords int;
DECLARE @StartRowIndex int;
SET @PageNumber = @PageNumber - 1
SET @StartRowIndex = (@PageNumber * @PageSize) + 1;

BEGIN
		WITH ProductEntries as (
			SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as 
						Row, 
						P.ProductID, 
						P.ProductName, 
						P.CategoryID,
						P.CurrentPrice,
						P.IsActive
			FROM Products P
			INNER JOIN Brands B
			ON P.BrandID = B.BrandID
			INNER JOIN Categories C
			ON P.CategoryID = C.CategoryID
			WHERE BrandName = @Brand and CategoryName = @Category
			)
			SELECT		ProductID, 
						CategoryID,						 
						ProductName
			FROM		ProductEntries
			WHERE		Row between @StartRowIndex and @StartRowIndex+@PageSize-1 and IsActive = 1
                        SELECT @TotalRecords = Count(*) FROM Products
			SET @NumberOfPages = dbo.GetTotalNumberOfPages(@PageSize, @TotalRecords)
	END

This is the GetTotalNumberOfPagesFunction that I use at the bottom


ALTER FUNCTION [dbo].[GetTotalNumberOfPages]
(
	@PageSize int,
	@TotalRecords int	
)
RETURNS  int
AS
BEGIN
	DECLARE @NumberOfPages int
	SET @NumberOfPages = CASE WHEN @TotalRecords % @PageSize = 0 THEN @TotalRecords/@PageSize
	ELSE
		@TotalRecords/@PageSize + 1
	END
		
	RETURN @NumberOfPages
END

any ideas?

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 12:06:56

Am I being thick or does @StartRowIndex need declaring somewhere?

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-16 : 22:15:30

quote:
Originally posted by Kristen

Am I being thick or does @StartRowIndex need declaring somewhere?

you are correct. i actually copied and pasted the relevant sections of a larger version of this proc to make it more 'web friendly', and in doing so forgot to copy over the @startrowindex. so my attempt to be concise has backfired. i've added the missing source to the original post above. thanks.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:22:33

"so my attempt to be concise has backfired"

hahaha ... No worries!

OK, need to understand what you are after here please:

"I am having trouble getting a total count of the result from my inner join"

You are doing paging based on


FROM Products P
INNER JOIN Brands B
ON P.BrandID = B.BrandID
INNER JOIN Categories C
ON P.CategoryID = C.CategoryID
WHERE BrandName = @Brand and CategoryName = @Category

but you are doing a COUNT(*) on only [Products].

Is it too easy to say change the COUNT(*) to:


SELECT @TotalRecords = Count(*)
FROM Products P
	INNER JOIN Brands B
		ON P.BrandID = B.BrandID
	INNER JOIN Categories C
		ON P.CategoryID = C.CategoryID
WHERE BrandName = @Brand and CategoryName = @Category

Kristen

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-17 : 01:41:16

Hi Kristen, ok here is the whole proc (forgive me it probably has other issues, ie isn't the most efficient looking thing).

Essentially it works fine but I want to return the @NumberOfPages as an output parameter so that in my front end I know how many page numbers to display. When a page number is selected by the user it will get passed back into the proc, along with the combination of @Brand and @Category parms chosen by the user - (this will determine how many records are returned, and how many pages are returned). The "IF" statements account for the different combinations of input of @Brand and @Category (see my commments in the code). I have to use the joins because the products table only returns the BrandID and the CategoryID, hence I have to join to the Brand and Category tables to get the full name of the brand and category (yes I know this seems bad because I am not using the PKEY but I am doing it like this because I am using URL_Rewriting, and hence need the BrandName to be the alternate PKEY (I ensure for uniqueness).

In the bottom "IF" statement I get the @NumberOfPages easy enough because the SELECT statement is returning ALL records.



[dbo].[prc_SelectProductsWithPaging]

ALTER Procedure [dbo].[prc_SelectProductsWithPaging]

@Brand varchar(30),
@Category  varchar(30),
@PageNumber int,
@PageSize int,
@NumberOfPages int output

AS

BEGIN

IF (@PageNumber is null)
BEGIN
	SET @PageNumber = 1
END

DECLARE @TotalRecords int;
DECLARE @StartRowIndex int;
SET @PageNumber = @PageNumber - 1
SET @StartRowIndex = (@PageNumber * @PageSize) + 1;

IF (@Brand = 'All Brands' and (@Category <> 'All Categories'))
	BEGIN
		-- get all brands and specific category
		WITH ProductEntries as (
			SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as 
						Row, 
						P.ProductID, 
						P.ProductName, 
						P.CategoryID,
						P.CurrentPrice,
						P.IsActive
			FROM Products P
			INNER JOIN Categories C
			ON P.CategoryID = C.CategoryID
			WHERE CategoryName = @Category
			)
			SELECT		ProductID, 
						CategoryID, 
						ProductName
			FROM		ProductEntries
			WHERE		Row between @StartRowIndex and @StartRowIndex+@PageSize-1 and IsActive = 1
	END
IF (@Brand <> 'All Brands' and (@Category = 'All Categories'))
	BEGIN
		-- get specific brand and all categories
		WITH ProductEntries as (
			SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as 
						Row, 
						P.ProductID, 
						P.ProductName, 
						P.CategoryID,
						P.CurrentPrice,
						P.IsActive
			FROM Products P
			INNER JOIN Brands B
			ON P.BrandID = B.BrandID
			WHERE BrandName = @Brand
			)
			SELECT		ProductID, 
						CategoryID, 
						ProductName
			FROM		ProductEntries
			WHERE		Row between @StartRowIndex and @StartRowIndex+@PageSize-1 and IsActive = 1
	END
IF (@Brand <> 'All Brands' and @Category <> 'All Categories')
	BEGIN
		-- get specific brand and specific category
		WITH ProductEntries as (
			SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as 
						Row, 
						P.ProductID, 
						P.ProductName, 
						P.CategoryID,
						P.CurrentPrice,
						P.IsActive
			FROM Products P
			INNER JOIN Brands B
			ON P.BrandID = B.BrandID
			INNER JOIN Categories C
			ON P.CategoryID = C.CategoryID
			WHERE BrandName = @Brand and CategoryName = @Category
			)
			SELECT		ProductID, 
						CategoryID,						 
						ProductName
			FROM		ProductEntries
			WHERE		Row between @StartRowIndex and @StartRowIndex+@PageSize-1 and IsActive = 1			
	END
IF (@Brand = 'All Brands' and @Category = 'All Categories')
	BEGIN
		-- get all brands and all categories		
		WITH ProductEntries as (
			SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as 
						Row, 
						P.ProductID, 
						P.ProductName, 
						P.CategoryID,
						P.CurrentPrice,
						P.IsActive						
			FROM Products P			
			)			
			SELECT		ProductID, 
						CategoryID, 
						ProductName,
						CurrentPrice						
			FROM		ProductEntries
			WHERE		Row between @StartRowIndex and @StartRowIndex+@PageSize-1 and IsActive = 1
			SELECT @TotalRecords = Count(*) FROM Products
			SET @NumberOfPages = dbo.GetTotalNumberOfPages(@PageSize, @TotalRecords)
	END

END

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:55:26

"I have to join to the Brand and Category tables to get the full name of the brand and category (yes I know this seems bad because I am not using the PKEY but I am doing it like this because I am using URL_Rewriting, and hence need the BrandName to be the alternate PKEY (I ensure for uniqueness)."

Doesn't seem bad to me, provided there are index(es) on those columns

You've only got


SELECT @TotalRecords = Count(*) FROM Products
SET @NumberOfPages = dbo.GetTotalNumberOfPages(@PageSize, @TotalRecords)

in the final "IF" block. Don't you need that in ever block, with the FROM/JOIN matching the tables in the "WITH ProductEntries" it refers to?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 02:12:26

Tried using NTILE() function?

DECLARE	@Sample TABLE (i INT)

INSERT		@Sample
		(
			i
		)
SELECT		TOP 90
		Number
FROM		master..spt_values
WHERE		Type = 'p'
ORDER BY	NEWID()

DECLARE	@TotalPages INT
SET	@TotalPages = 25

SELECT	i,
	NTILE(@TotalPages) OVER (ORDER BY i) AS Page
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-17 : 03:06:18

KIRSTEN:

in the final "IF" block. Don't you need that in ever block, with the FROM/JOIN matching the tables in the "WITH ProductEntries" it refers to?

yes i tried putting it in every block. the catch is however that every block will likely return a different number of records because the @Brand and @Category will be different - for example, when i add this in the second last "IF" block like the following

SELECT @TotalRecords = Count(*) FROM Products WHERE BrandName = @Brand and CategoryName = @Category
SET @NumberOfPages = dbo.GetTotalNumberOfPages(@PageSize, @TotalRecords)

it gives me the following error:

Msg 207, Level 16, State 1, Procedure prc_SelectProductsWithPaging, Line 90
Invalid column name 'BrandName'.
Msg 207, Level 16, State 1, Procedure prc_SelectProductsWithPaging, Line 90

Invalid column name 'CategoryName'.

PESO:

I am not familiar with NTILE, but after reading the definition on BOL I am not sure how it would help me (unless I am missing something)?

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 03:49:46

You need the JOINED tables, so for the second-to-last one that would be:


SELECT @TotalRecords = Count(*)
FROM Products P
	INNER JOIN Brands B
		ON P.BrandID = B.BrandID
	INNER JOIN Categories C
		ON P.CategoryID = C.CategoryID
WHERE BrandName = @Brand and CategoryName = @Category

Kristen

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-17 : 04:03:58

Kristen, nice one! I was just looking at that I can't believe I missed it.

I think what I was really hoping was to try and get the Count in the block using the WITH clause - it would just seem prettier/more concise then having to create another SELECT/JOIN just to get the count.

Do you think that is possible?

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 04:31:57

Well I'm still doing it the SQL 2000 way

so we do


INSERT INTO #SomeTempTable
SELECT MyPK
FROM MyTable1
    JOIN LotsOfOtherTables
        ON LotsOfColumns
WHERE LoadsOfCriteria
SELECT @intRowCount = @@ROWCOUNT

and then join #SomeTempTable to the tables we need for the "presentation" columns, using an Identity column to reach only the relevant rows equivalent to the Page required.

If we didn't need the PageCount we could save that whole working-copy thing.

But I don't know of a way to avoid your additional COUNT(*) step, but I'm all ears for any available solution.

I'd better go read about NTILE() because I hadn't heard of that before Peso raised it. But I'm used to that, he's got a memory like an Elephant!

Kristen

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-17 : 04:44:56

no worries thanks Kristen. yeh i jumped over to 2005 specifically for the row functionality to help speed up my paging, and i find it handles blobs better so helps me with more with efficient images (although i still try avoid it where possible).

cheers again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 05:30:43

[code]ALTER PROCEDURE dbo.usp_SelectProductsWithPaging
(
@Brand VARCHAR(30),
@Category VARCHAR(30),
@PageNumber INT,
@PageSize INT,
@NumberOfPages INT OUTPUT
)
AS

SET NOCOUNT ON

DECLARE @StartRowIndex INT,
@EndRowIndex INT

SELECT @PageNumber = COALESCE(@PageNumber, 1) - 1,
@StartRowIndex = @PageNumber * @PageSize + 1,
@EndRowIndex = @StartRowIndex + @PageSize - 1

DECLARE @Stage TABLE (ProductID INT, CategoryID INT, ProductName VARCHAR(30), TotalRows INT)

;WITH ProductEntries (ProductID, CategoryID, ProductName, RowNumber, TotalRows)
AS (
SELECT p.ProductID,
p.CategoryID,
p.ProductName,
ROW_NUMBER() OVER (ORDER BY p.ProductID),
COUNT(*) OVER (PARTITION BY NULL)
FROM Products AS p
INNER JOIN Categories AS c ON c.CategoryID = p.CategoryID
WHERE p.IsActive = 1
AND @Category <> 'All Categories'
AND c.CategoryName = @Category
AND @Brand = 'All Brands'

UNION ALL

SELECT p.ProductID,
p.CategoryID,
p.ProductName,
ROW_NUMBER() OVER (ORDER BY p.ProductID),
COUNT(*) OVER (PARTITION BY NULL)
FROM Products AS p
INNER JOIN Brands AS b ON b.BrandID = p.BrandID
WHERE p.IsActive = 1
AND @Brand <> 'All Brands'
AND b.BrandName = @Brand
AND @Category = 'All Categories'

UNION ALL

SELECT p.ProductID,
p.CategoryID,
p.ProductName,
ROW_NUMBER() OVER (ORDER BY p.ProductID),
COUNT(*) OVER (PARTITION BY NULL)
FROM Products AS p
INNER JOIN Brands AS b ON b.BrandID = p.BrandID
INNER JOIN Categories AS c ON c.CategoryID = p.CategoryID
WHERE p.IsActive = 1
AND @Brand <> 'All Brands'
AND b.BrandName = @Brand
AND @Category <> 'All Categories'
AND c.CategoryName = @Category

UNION ALL

SELECT p.ProductID,
p.CategoryID,
p.ProductName,
ROW_NUMBER() OVER (ORDER BY p.ProductID),
COUNT(*) OVER (PARTITION BY NULL)
FROM Products AS p
WHERE p.IsActive = 1
AND @Brand = 'All Brands'
AND @Category = 'All Categories'
)

INSERT @Stage
(
ProductID,
CategoryID,
ProductName,
TotalRows
)
SELECT ProductID,
CategoryID,
ProductName,
TotalRows
FROM ProductEntries
WHERE RowNumber BETWEEN @StartRowIndex AND @EndRowIndex

SELECT @NumberOfPages = CEILING(MAX(TotalRows) / @PageSize)
FROM @Stage

SELECT ProductID,
CategoryID,
ProductName
FROM @Stage[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 06:16:22

COUNT(*) OVER (PARTITION BY NULL)

How cool is THAT!

One of these days our clients will start forking out for SQL 2005 upgrades ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 06:30:38

It's a neat little trick...

E 12°55'05.25"
N 56°04'39.16"

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-17 : 10:17:50

nice one Peso. That works a treat. I always find something new to learn at this place!

I did notice a couple of things with the proc:

- when the incoming @PageSize is greater than or less than the actual number of records, the @TotalPages OUTPUT parameters shows 0
- when the @PageSize value is equal to the actual number of rows the @TotalPages parameters shows up as 1.

I was hoping that @TotalPages would always return 1 page if there is at least one record to show.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 10:27:58

I think you can do with just this one select

;WITH ProductEntries (ProductID, CategoryID, ProductName, RowNumber, TotalRows)
AS (
	SELECT		p.ProductID,
			p.CategoryID,
			p.ProductName,
			ROW_NUMBER() OVER (ORDER BY p.ProductID),
			COUNT(*) OVER (PARTITION BY NULL)
	FROM		Products AS p
	INNER JOIN	Brands AS b ON b.BrandID = p.BrandID
	INNER JOIN	Categories AS c ON c.CategoryID = p.CategoryID
	WHERE		p.IsActive = 1
			AND @Brand IN (b.BrandName, 'All Brands')
			AND @Category IN (c.CategoryName, 'All Categories'))
)

But I have no idea of performance for your environment. Test?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 10:32:15

Ok, you do not need integer division.

Try this instead

SELECT	@NumberOfPages = CEILING(1.0 * MAX(TotalRows) / @PageSize)
FROM	@Stage

E 12°55'05.25"
N 56°04'39.16"

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-17 : 11:28:24

thanks again Peso. That works well. I've only got a small amount of data but I'll plug some more in to get a better idea of performance. It will be interesting to see which one is faster.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 12:31:12

You initial separate-code-blocks for each combination of parameters is almost certainly the most performant. Although you may actually have to split out each code block into a separate SProc so it gets its own query plan. But its the most fragile in maintenance - if you change a bit and then have to remember to change all the other.

Its a Trade-off as usual! and testing it is probably your best way to decide if performance is OK, or not.

Kristen

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-18 : 04:45:22

Thanks Kristen, I think I'll probably create the separate procs as you suggest (when the result set gets too large.

Just out of curiosity would you recommend performing the paging in the "child-procs" or pass the results back to the main proc in the form of a table, and then do the paging on that (if u know what I mean)?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources