Dynamic Exists Problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Dynamic Exists Problem

Author

Topic

acko
Yak Posting Veteran

52 Posts

Posted - 2003-06-06 : 04:40:18

/*
Hi, I have the folowing T-SQL problem.
Here are the tables that I use for my query.

The first table is Categories. In this table there are the
names of attributes that are used for classification of Products.

*/
CREATE TABLE [Categories] (
[CategoryID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[CategoryName] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,

CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

/*
The second is CategoryValues in which contains the values for every categories.
Every category can have more then one values with their descriptions.

*/
CREATE TABLE [CategoryValues]
(
[CategoryValues] [nvarchar] (10) NOT NULL ,
[CategoryID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[CategoryDescryption] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_CategoryValues] PRIMARY KEY CLUSTERED
(
[CategoryValues],
[CategoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_CategoryValues_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [Categories] (
[CategoryID]
)
) ON [PRIMARY]
GO
/*
The next table is Products.

*/
CREATE TABLE [Products]
(
[ProductID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProductName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/*
And finally ProductsCategory. It connects products with categories.
*/
CREATE TABLE [ProductsCategory]
(
[ProductID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[CategoryID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[CategoryValues] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_ProductsCategory] PRIMARY KEY CLUSTERED
(
[ProductID],
[CategoryID],
[CategoryValues]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProductsCategory_CategoryValues] FOREIGN KEY
(
[CategoryValues],
[CategoryID]
) REFERENCES [CategoryValues] (
[CategoryValues],
[CategoryID]
),
CONSTRAINT [FK_ProductsCategory_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [Products] (
[ProductID]
)
) ON [PRIMARY]
GO
/*
I hope that I'll succeed to explain what i realy need. The real problem is more
complicated because there are a few tables more, something like order and
orderdetails in NorthWind, from which I have to calculate some cumulative results.

I need to see all from products where products belong to certain category.
This is not realy hard. So I wrote this query:

*/
SELECT *
FROM Products p

WHERE EXISTS
(
SELECT p1.productid
FROM Products p1 JOIN ProductsCategory pc ON p.productid=pc.productid
JOIN CategoryValues cv ON pc.CategoryValues = cv.CategoryValues
AND pc.CategoryId = cv.CategroryID
WHERE cv.CategoryValues ='01' and cv.CategoryId = '14'
AND p1.productid = p.productid
)

/*
But real problem is that I have to see which products belong for two or more categories for
which CategoryValues are some variables like categories too.
And I do not know how many categories user will send.
So if he send two categories I will add one more exists so the Where clause becomes:

*/
WHERE EXISTS
(
SELECT p1.productid
FROM Products p1 JOIN ProductsCategory pc ON p.productid=pc.productid
JOIN CategoryValues cv ON pc.CategoryValues = cv.CategoryValues
AND pc.CategoryId = cv.CategroryID
WHERE cv.CategoryValues ='01' and cv.CategoryId = '14'
AND p1.productid = p.productid
) and
EXISTS
(
SELECT p1.productid
FROM Products p1 JOIN ProductsCategory pc ON p.productid=pc.productid
JOIN CategoryValues cv ON pc.CategoryValues = cv.CategoryValues
AND pc.CategoryId = cv.CategroryID
WHERE cv.CategoryValues ='03' and cv.CategoryId = '1162'
AND p1.productid = p.productid
)
/*
If the user send one more category I will have to add one more exists...
All this would have to be in one stored proc which user will call from VB6.
So I do not know how many parameters user will send.
Is there any way to pack all of this in one stored proc and not to use dynamic sql
or if I have to use dynamic sql can anyone explain me how.

If there is no way, I think
that I have to generate query from vb and to send to sql server.
Thanks very much.
*/

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-06 : 07:15:42

First of all, I suspect you need to do some sort of group by with a having clause ..


select
    p.productname
from
    products p
    inner join productscategories pc
        on p.productid = pc.productid
    inner join categoryvalues cv
        on pc.categoryvalues = cv.categoryvalues
where
    cv.categoryvalues in ('01','03') and
    cv.categoryid in ('14','1162')
group by
    p.productname
having
    count(*) > 1

However, I must say, I don't understand your schema. Maybe some sample data would clear it up, but why have categoryvalues in the productscategory table? Is categoryvalues an attribute of the category or an attribute of the product-category relationship?

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-06 : 07:16:45

This is not the exact answer to your problem, but take a look.

First, if users can enter more than 1 possible category to search, consider allowing them to enter a comma-seperated list of categories they want:

i.e., the @Categories parameter might be passed a string like '12,34,15,20'

Then, search this site for how to turn this CSV into a table variable. There are many methods. That would be the first step in your stored procedure, take that @categories parameter and insert each value listed into a table variable.

so, after doing that, you might have a table variable called @t, with a field called "cat" with these values:

12
34
15
20

And SELECT COUNT(*) FROM @t tells you how many values the user has entered.

Then, without using dynamic SQL, here is a simplified version of what you are trying to get:

SELECT ProductID, COUNT(DISTINCT Cats.Cat) as Matches
FROM
Products
INNER JOIN
@t Cats
ON
Cats.Cat = Products.Cat
GROUP BY
ProductID

That returns all products that have any matches in the categories list, along with how many matches there are. We only want to return complete matches, so take a look at this:

SELECT A.*
FROM
Products A
INNER JOIN
(above SQL) B
ON
A.ProductID = B.ProductID
WHERE B.Matches = (SELECT COUNT(*) FROM @t)

That says, return all the rows from the Products table where the # of matches to the values in your table variable = the # of values in the table variable, which is the data you want to return.

Your stuff is more complex (i.e., more joins and all that) but hopefully this gives you some ideas. It's the technique more than the actual answer I'd like to convey.

And, in case you didn't notice, NO dynamic SQL and the user can pass as many categories as they want (within the limits of a 8000 character string).

Hope this helps.
INNER JOIN

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-06 : 07:35:02

You could look at the IN operator.

I'd consider rewriting your first query as:

SELECT P.* -- Only the items from table Products

FROM Products P

JOIN ProductsCategory pc ON p.productid=pc.productid

JOIN CategoryValues cv ON pc.CategoryValues = cv.CategoryValues AND pc.CategoryId = cv.CategroryID

WHERE cv.CategoryValues ='01' and cv.CategoryId = '14'

(I wonder if INNER JOIN could be used instead of JOIN in this query?)

Next, it's possible to use the IN operator to check for multiple occurances, but first, the CValue / CID pair must be a unique entity. One solution is to add them as strings to form a unique index:

WHERE cv.CategoryValues + cv.CategoryID IN (build the table of values here)

The animal to the right of IN must be a table of values, in this case, all the Value/ID pairs entered by the user. Builing a table like this is another matter. You could just insert them into a temporary table #MyList, or you could look at building a user defined function (see [url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=25830[/url]) to build the table from a comma delimited string of values? (You didn't say much about how the user values are provided.

HTH,

Sam

Edited by - SamC on 06/06/2003 07:38:23

acko
Yak Posting Veteran

52 Posts

Posted - 2003-06-06 : 11:02:32

quote:

This is not the exact answer to your problem, but take a look.

First, if users can enter more than 1 possible category to search, consider allowing them to enter a comma-seperated list of categories they want:

i.e., the @Categories parameter might be passed a string like '12,34,15,20'

Then, search this site for how to turn this CSV into a table variable. There are many methods. That would be the first step in your stored procedure, take that @categories parameter and insert each value listed into a table variable.

so, after doing that, you might have a table variable called @t, with a field called "cat" with these values:

12
34
15
20

And SELECT COUNT(*) FROM @t tells you how many values the user has entered.

Then, without using dynamic SQL, here is a simplified version of what you are trying to get:

SELECT ProductID, COUNT(DISTINCT Cats.Cat) as Matches
FROM
Products
INNER JOIN
@t Cats
ON
Cats.Cat = Products.Cat
GROUP BY
ProductID

That returns all products that have any matches in the categories list, along with how many matches there are. We only want to return complete matches, so take a look at this:

SELECT A.*
FROM
Products A
INNER JOIN
(above SQL) B
ON
A.ProductID = B.ProductID
WHERE B.Matches = (SELECT COUNT(*) FROM @t)

That says, return all the rows from the Products table where the # of matches to the values in your table variable = the # of values in the table variable, which is the data you want to return.

Your stuff is more complex (i.e., more joins and all that) but hopefully this gives you some ideas. It's the technique more than the actual answer I'd like to convey.

And, in case you didn't notice, NO dynamic SQL and the user can pass as many categories as they want (within the limits of a 8000 character string).

Hope this helps.
INNER JOIN

- Jeff

acko
Yak Posting Veteran

52 Posts

Posted - 2003-06-06 : 11:06:24

Copy mistake
Thank you all for your responses
Jeff answer was near of my needs
I found something that realy helped me so here it is

CREATE TABLE #T
(
catid NVARCHAR(2) NOT NULL,
catval NVARCHAR(10) NOT NULL,
PRIMARY KEY(catid, catval)
)

INSERT INTO #T VALUES('01', '111')
INSERT INTO #T VALUES('02', '222')

SELECT *
FROM Products AS P
WHERE NOT EXISTS
(SELECT *
FROM #T AS T
WHERE NOT EXISTS
(SELECT *
FROM ProductsCategory AS PC
WHERE PC.ProductID = P.ProductID
AND PC.CategoryID = T.catid
AND PC.CategoryValues = T.catval))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources