/*
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.
*/

First off, in the future, PLEASE edit out those ugly COLLATE clauses in your DLL. Microsoft really should make their inclusion an option in the script generation. Their presence adds nothing to our discussion and they just make the DDL hard to read. But thank you very much for including the DLL in your post, as it makes things easier, since we don't have to make assumptions about your table structures.

There is good news and bad news.

The good news is you really don't need all those EXISTS() predicates in your WHERE clause.

Thus, to select products where the CategoryValues column is '01' and the CategoryId is '14', use simply:

SELECT p.*
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'

Note that it isn't usually a good idea to use the SELECT * syntax in production code. We use it in forums such as this as a shorthand way to say 'SELECT the columns you need', but you'll be better off explicitly listing the columns you need, even if it is a bit more typing. Doing this allows you to control the order of the columns in the resultset, and makes your code resilient to changes in the table schema; if somebody adds a new columnm to your table, your code won't suddenly break because it bumps into a column it didn't expect.

To handle multiple CategoryValues and CategoryId values, just extend the WHERE clause:

WHERE (cv.CategoryValues ='01' and cv.CategoryId = '14')
   OR (cv.CategoryValues ='03' and cv.CategoryId = '1162')

The bad news is that there is no simple way to extend this to an arbitrary set of values. There are a few alternatives:

1. Dynamic SQL - build your query into a string variable bby concatentating all the clasues into a string variable then EXECecuting it. IMO, this should be your last choice as it will be slow and if you are not careful, it can be insecure - you can leave yourself open to SQL injection attacks.

2. Build the query in the VB client and use the provider's execute capability. This is a bit better becuase it will be more secure, but it will still be slow.

3. Place the user's selection criteria into a table and join that table to your original query, thus implicitly selecting only the rows which match, as:

SELECT p.*
FROM Products p JOIN ProductsCategory pc ON p.productid=pc.productid
JOIN CategoryValues cv ON pc.CategoryValues = cv.CategoryValues
AND pc.CategoryId = cv.CategroryID
JOIN TempTable T ON T.CategoryValues = cv.CategoryValues
AND T.CategoryId = cv.CategroryID

4. If you are using SQL Server 2000, pass the selection criteria into the stored procedure as a string, parse the sting into a table variable, and JOIN the table variable to you query as above. If you search the P2P SQL Server Archives for "FUNCTION InList" - it is a user defined function I wrote and posted some time ago which parses a comma delimited string into a table variable - you should be able to adopt it for your use.

Good luck,

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thank you very much for your answers.
Best regards
Acko