July 13, 2011 at 12:59 am
Hi - I am fairly new at using SQL server in a development environment.
I am currently creating reports in Visual Studio for SRSS - These reports use our Dynamics CRM database.
We have setup a relationship between the product table and the accounts table - The idea is that we know what accounts have which products.
I want to run an exception report for our core range of products. I want to see the Account details and the products that are missing from the core range.
To be honest I feel like an idiot - I have tried using EXISTS and NOT EXISTS clauses but I just can't figure out the syntax for it at all.
I would appreciate any help at all
The relevant table structure is the following
Account
Account ID, Account Name, Category
Product
Product ID, Product Name, Range
Product_Account
Product ID, Account ID, Product_AccountID
July 13, 2011 at 1:17 am
Trying to understand... Rows from Account where there's no matching row in Product_Account?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2011 at 1:31 am
The return I want is by the Account Name, and the Product Names of the Core range that is missing.
Account Table
Account Name
AAA
BBB
Product Table
Product Name, Range
ProdAA, Core
ProdBB, 2nd
ProdCC, COre
AccountProduct Table
Account, Product
AAA, ProdAA
BBB, ProdAA
BBB, ProdCC
The output I would expect would be
Account Name, Product Name
AAA, ProdCC
July 13, 2011 at 2:03 am
Why is ProdCC missing? It's listed in the AccountProducts table, the accountID joins to a valid row in Accounts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2011 at 4:56 am
What exactly do you mean by "core range"?
July 13, 2011 at 5:13 am
With the follwoing test data:
CREATE TABLE #Accounts
(
AccountName varchar(25) NOT NULL
)
INSERT INTO #Accounts VALUES ('AAA'),('BBB')
CREATE TABLE #Products
(
ProductName varchar(25) NOT NULL
,[Range] varchar(25) NOT NULL
)
INSERT INTO #Products VALUES ('ProdAA','Core'),('ProdBB','2nd'),('ProdCC','Core')
CREATE TABLE #AccountProduct
(
AccountName varchar(25) NOT NULL
,ProductName varchar(25) NOT NULL
)
INSERT INTO #AccountProduct VALUES ('AAA','ProdAA'),('BBB','ProdAA'),('BBB','ProdCC')
maybe:
SELECT A.AccountName, P.ProductName
FROM #Accounts A
CROSS JOIN #Products P
WHERE P.[Range] = 'Core'
EXCEPT
SELECT AccountName, ProductName
FROM #AccountProduct
or using EXISTS:
SELECT A.AccountName, P.ProductName
FROM #Accounts A
CROSS JOIN #Products P
WHERE P.[Range] = 'Core'
AND NOT EXISTS
(
SELECT *
FROM #AccountProduct AP
WHERE AP.AccountName = A.AccountName
AND AP.ProductName = P.ProductName
)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply