Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 6th, 2003, 03:45 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Yugoslavia.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dynamic Exists Problem

/*
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.
*/
Reply With Quote
  #2 (permalink)  
Old June 6th, 2003, 07:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 6th, 2003, 09:54 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Yugoslavia.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much for your answers.
Best regards
Acko
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Image Problem pete robinson Classic ASP Basics 3 December 6th, 2007 09:39 AM
dynamic sitemap problem yiyo ASP.NET 2.0 Professional 0 May 2nd, 2007 04:29 PM
Dynamic Display Problem gabster XSLT 2 March 5th, 2007 03:02 PM
Dynamic query problem drex10 SQL Server 2000 2 April 6th, 2004 11:55 PM
Problem with Dynamic DataGrid athariqbal General .NET 0 March 31st, 2004 01:45 AM



All times are GMT -4. The time now is 03:33 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.