stored procedure is working wrong

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)

stored procedure is working wrong

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-11-10 : 06:15:24

Dear All,

I have 3 tables,

Products:-
productId smallint
productName nvarchar(256)
Deleted bit

Branches:-
branchId smallint
branchName nvarchar(256)
fk_productId smallint
Deleted bit

Builds:-
buildId int
buildName nvarchar(256)
fk_branchId smallint
fk_productid smallint
Deleted bit

Now I am building a tree of Products-Branches-Builds, and that is working fine.

What I want to do now is,

if a product is Deleted, do not display any Branches or Builds for that Product
if a branch is Deleted, do not display any Builds for that Branch
if a build is Deleted, do not display that particular Build

And I have this stored procedure:-

SELECT TOP (100) PERCENT ISNULL(dbo.Products.productID, 0) AS ProductID, COALESCE (dbo.Products.productName, '') AS ProductName,
ISNULL(dbo.CrawledBuilds.crawledBuildId, 0) AS CrawledBuildId, COALESCE (dbo.CrawledBuilds.CrawledBuildName, '') AS CrawledBuildName,
dbo.CrawledBuilds.CrawledUrl, ISNULL(dbo.CrawledBuilds.fk_CrawledBuildStatus, 0) AS fk_CrawledBuildStatus, ISNULL(dbo.Branches.branchID, 0) AS BranchId,
COALESCE (dbo.Branches.branchName, '') AS BranchName,
COALESCE (Products.Deleted, 0) AS ProductsDeleted,
COALESCE (Branches.Deleted, 0) AS BranchesDeleted,
COALESCE (CrawledBuilds.Deleted, 0) AS BuildsDeleted
FROM dbo.Products FULL OUTER JOIN
dbo.Branches ON dbo.Products.productID = dbo.Branches.fk_ProductId FULL OUTER JOIN
dbo.CrawledBuilds ON dbo.Products.productID = dbo.CrawledBuilds.fk_ProductId AND dbo.Branches.branchID = dbo.CrawledBuilds.fk_BranchId
WHERE ( (dbo.Products.Deleted = 0) AND (dbo.Branches.Deleted = 0) AND (CrawledBuilds.Deleted = 0) )
ORDER BY dbo.Products.productName, dbo.Branches.branchName, CrawledBuildName

This is the present data:-

ProductID BranchID BuildID ProductDeleted BranchDeleted BuildDeleted
9 5 0 0 1 0
6 3 0 0 0 0
7 4 0 0 0 0
8 0 0 0 0 0
4 2 0 0 0 0
1 1 30013 0 0 0
1 1 28 0 0 0
1 1 30002 0 0 0
1 1 30004 0 0 0
1 1 30007 0 0 0
2 6 30009 0 0 0
2 6 30005 0 0 0
2 6 30007 0 0 0
2 6 28 0 0 0
2 6 30 0 0 0
2 6 30012 0 0 0
2 6 30014 0 0 0
3 7 30016 0 0 0
3 7 30015 0 0 0
3 7 30020 0 0 0
3 7 30026 0 0 0

However i am not getting the correct results. For example, this store procedure, instead of ignoring just the first row, its also ignoring the first 5 rows

Can anybody give me some light on what I am doing wrong

Thanks a lot for your help and time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:20:22

Does this make a difference?

SELECT TOP (100) PERCENT ISNULL(dbo.Products.productID, 0) AS ProductID, COALESCE (dbo.Products.productName, '') AS ProductName, 
ISNULL(dbo.CrawledBuilds.crawledBuildId, 0) AS CrawledBuildId, COALESCE (dbo.CrawledBuilds.CrawledBuildName, '') AS CrawledBuildName, 
dbo.CrawledBuilds.CrawledUrl, ISNULL(dbo.CrawledBuilds.fk_CrawledBuildStatus, 0) AS fk_CrawledBuildStatus, ISNULL(dbo.Branches.branchID, 0) AS BranchId, 
COALESCE (dbo.Branches.branchName, '') AS BranchName, 
COALESCE (Products.Deleted, 0) AS ProductsDeleted, 
COALESCE (Branches.Deleted, 0) AS BranchesDeleted, 
COALESCE (CrawledBuilds.Deleted, 0) AS BuildsDeleted
FROM dbo.Products 
FULL OUTER JOIN dbo.Branches 
ON dbo.Products.productID = dbo.Branches.fk_ProductId 
AND dbo.Products.Deleted = 0
AND dbo.Branches.Deleted = 0
FULL OUTER JOIN dbo.CrawledBuilds 
ON dbo.Products.productID = dbo.CrawledBuilds.fk_ProductId 
AND dbo.Branches.branchID = dbo.CrawledBuilds.fk_BranchId
AND CrawledBuilds.Deleted = 0
ORDER BY dbo.Products.productName, dbo.Branches.branchName, CrawledBuildName

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-11-10 : 06:26:23

its returning an extra row at the top

ProductID BranchID BuildID ProductDeleted BranchDeleted BuildDeleted
0 5 0 0 1 0

which should not be there.

Also this branch is attached to productId 9

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:42:21

[code]SELECT TOP (100) PERCENT ISNULL(dbo.Products.productID, 0) AS ProductID, COALESCE (dbo.Products.productName, '') AS ProductName,
ISNULL(t.crawledBuildId, 0) AS CrawledBuildId, COALESCE (t.CrawledBuildName, '') AS CrawledBuildName,
dbo.CrawledBuilds.CrawledUrl, ISNULL(t.fk_CrawledBuildStatus, 0) AS fk_CrawledBuildStatus, ISNULL(t.branchID, 0) AS BranchId,
COALESCE (t.branchName, '') AS BranchName,
COALESCE (Products.Deleted, 0) AS ProductsDeleted,
COALESCE (t.Deleted, 0) AS BranchesDeleted,
COALESCE (t.Deleted, 0) AS BuildsDeleted
FROM dbo.Products
FULL OUTER JOIN
(SELECT dbo.CrawledBuilds.fk_ProductId,
dbo.Branches.branchName, dbo.CrawledBuilds.CrawledBuildName,
dbo.CrawledBuilds.crawledBuildId,dbo.CrawledBuilds.CrawledUrl,dbo.CrawledBuilds.fk_CrawledBuildStatus
dbo.Branches.branchID,dbo.Branches.branchName,Branches.Deleted AS BDeleted,CrawledBuilds.Deleted AS CDeleted
FROM dbo.Branches
INNER JOIN dbo.CrawledBuilds
ON dbo.Branches.ProductId = dbo.CrawledBuilds.fk_ProductId
AND dbo.Branches.branchID = dbo.CrawledBuilds.fk_BranchId
AND CrawledBuilds.Deleted = 0)t
ON dbo.Products.productID =t.fk_ProductId
AND dbo.Products.Deleted = 0
AND t.BDeleted = 0
ORDER BY dbo.Products.productName, t.branchName, CrawledBuildName[/code]

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-11-10 : 06:51:20

ok solved!

SELECT
ISNULL(p.productID, 0) AS ProductID,
COALESCE (p.productName, '') AS ProductName,
ISNULL(c.crawledBuildId, 0) AS CrawledBuildId,
COALESCE (c.CrawledBuildName, '') AS CrawledBuildName,
c.CrawledUrl,
ISNULL(c.fk_CrawledBuildStatus, 0) AS fk_CrawledBuildStatus,
ISNULL(b.branchID, 0) AS BranchId,
COALESCE (b.branchName, '') AS BranchName,
COALESCE (p.Deleted, 0) AS ProductsDeleted,
COALESCE (b.Deleted, 0) AS BranchesDeleted,
COALESCE (c.Deleted, 0) AS BuildsDeleted

FROM dbo.Products p

FULL OUTER JOIN dbo.Branches b ON p.productID = b.fk_ProductId AND b.Deleted = 0

FULL OUTER JOIN dbo.CrawledBuilds c ON p.productID = c.fk_ProductId AND b.branchID = c.fk_BranchId AND c.Deleted = 0

WHERE p.Deleted = 0

ORDER BY p.productName, b.branchName, CrawledBuildName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources