stored procedure is not working
-
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
-
i posted sample data
-
Hi Johann
Your problem was almost certainly caused by including the deleted filters in your where clause. Put them into the join conditions instead:
[font="Courier New"]SELECT TOP (100) PERCENTISNULL(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
[/font]
Note that for readability I've modified your code to use table aliases. You might want to determine whether full outer joins are necessary, and whether or not you need the TOP clause.
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
working excellently now
but in the where clause why just
WHERE p.Deleted = 0
-
ah ok
I get what you are doing.
You are filtering every step by the deleted, and then everything should be visible if the products.deleted is 0 obv
Excellent stuff
Thanks for your help !
much appreciated
-
Johann Montfort (11/10/2008)
working excellently nowbut in the where clause why just
WHERE p.Deleted = 0
If you have branches.deleted = 0 in the WHERE clause, then your output will have exactly that...only rows where branches.deleted = 0: effectively giving you an INNER JOIN between products and branches.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
yes excellent
Thanks a lot Chris!
-
Johann Montfort (11/10/2008)
i posted sample dataNot really. You posted a big blob of text. Read the post Hodgy tried to point you to (also in my signature) for an example of what we mean by sample data. Providing data in this fashion will get you better answers that people can actually test without going through a lot of extra work to try to recreate your environment first.
-
ah ok for next time then
Thanks for the help
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply