sql statement help (beginner :~) )

Here is what my current SQL statement looks like:

SELECT Customers.*
FROM Customers INNER JOIN
CustomerCatalogs ON Customers.CustomerID = CustomerCatalogs.CustomerID
WHERE (CustomerCatalogs.CatalogID = @catID)
ORDER BY Customers.CompanyName
There’s also a field called “NumbSend” in CustomerCatalogs. NumbSend is the number of catalogs of this type to send with this customer.
So CustomerCatalogs contains:
CatalogID
CustomerID
NumbSend
Where customerID is the customer’s ID, catalogID is the catalogs ID, and NumbSend is the number of those catalogs to send to that customer.
What customer contains isn’t important.
The above statement will get me each customer for that catalogID (@catID) once, but not for the number of times I need them for (NumbSend). What I want is the customer added to the return table the number of times NumbSend says it should be.
How do I do a for loop type operation that will add the customer multiple times over and over again for the value of NumbSend?
0
bluewolf07
8/1/2003 4:24:43 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

6 Replies
314 Views

Similar Articles

[PageSpeed] 51

I am trying to figure this out.

Is the problem that you need to generate a label for each catalogue you wish to send to a client, and that client can have 1 to many labels?
If so, why not add the numsend value and loop in your program?

SELECT Customers.*, CustomerCatalogs.NumSend
FROM Customers INNER JOIN
CustomerCatalogs ON Customers.CustomerID = CustomerCatalogs.CustomerID
WHERE (CustomerCatalogs.CatalogID = @catID)
ORDER BY Customers.CompanyName

Then from your program you loop, not from the database.
If you MUST loop from the database, you should look up Cursors in the BOL (Books On Line). They are much slower and less efficient, but sometimes ya just gotta. ;)

Ask One/Answer One.
0
jmurdock
8/1/2003 2:55:37 PM
bossman claims always use SQL cause it's always faster.  You gotta do what bossman says.  Acutally as of this afternoon this is no longer an issue for me.  They changed their mind, so thanks for you help.  

0
bluewolf07
8/1/2003 6:08:10 PM
bossman isnt always bossman because he knows the facts. He is usually bossman because he is better at giving orders (telling others what to build) than he is at executing the orders (developing software).

And in this case, the bossman's statement "always use SQL cause it's always faster" is just plain wrong and misinformed.

Keith Rome


MCSD, MCDBA, MCAD, CIC

0
krome
8/1/2003 6:33:19 PM
Okay,

Leaving out whether the boss knows anything at all (I would agree that MOST database functions/updates are done faster by staying in the database. NOT All).
Was my question correct?
Is that in fact what you are attempting to do?
Oh, and unless you are programming in some really OLD program which does not compile, like QBasic or something, I would expect that it is faster and more efficient to loop in the program. This should allow you to get results faster (not much, but faster) and instead of putting un-necessary load on a database server which will likely affect a large number of people, you can put the load onto a desktop where the clock cycles only affect one user.
But, like I said, we will ignore that.
Now, are you in fact printing labels?
You might try something like this in your SP:
<code>
Declare Table #TempValues (put in your fields)
Declare @field1 int,
@Field2 varchar(50), ...
@NumbSend int,
@intCounter int

Declare curLabels Cursor for SELECT Customers.field, Customers.Field... ,
CustomerCatalogs.NumbSend
FROM Customers INNER JOIN
CustomerCatalogs ON Customers.CustomerID = CustomerCatalogs.CustomerID
WHERE (CustomerCatalogs.CatalogID = @catID)
ORDER BY Customers.CompanyName
Open curLabels
Fetch Next From curLabels
Into @field, @field, ... @NumbSend
While @@Fetch_status = 0
Begin
intCounter = 0
While intCounter < @NumbSend + 1
Begin
Insert into #TempValues (field, field, field...)
Values @field, @field, @field
intCounter = intCounter + 1
End
Fetch Next From curLabels
Into @field, @field, ... @NumbSend
End
Close CurLabels
Deallocate CurLabels
Select field, field, field
from #TempValues
<code>
Obviously I don't know what your field names are so I can't use them.
Basically, I would ... (Well I wouldn't but you can)
Make a temporary table (# means temporary in SQL store Procedures)
Make a cursor
Load the cursor with ONE Record which includes the counter
Open the cursor
Read a line,
Loop through and insert a record in the temp table for X times
Close the cursor
Deallocate the cursor
select from the temp table.
Also note, you should not use Select *. Doing so will slow the query result, it will also put at risk any stored procedures or program code which rely on THIS stored procedure since a table change will change the output.

Ask One/Answer One.
0
jmurdock
8/1/2003 8:13:30 PM
Yes I am printing labels.  A label for each catalog that is to be sent a certain number of times to a customer.
0
bluewolf07
8/2/2003 12:08:19 AM
Howdy,

Here's one of many answers to this age old riddle. One answer is to use an auxiliary table...and yes, your boss is right. The relational implementation will more than likely outperform any cursor based solution (given indexing, hardware and other factors remain equal). I just threw together this script, without much regard to your original query, but you should be able to derive your solution based on the code that I'm providing.
This solution relies on having a numbers table, and using a greater than or equal to join. I hope this solves your problem, and you can go back to your boss and say that you're sorry, but also that you figured out how to solve the problem relationally and will save the company thousands of dollars in hardware costs from not overutilizing the server needlessly!

-- Start Script
-- Create a customer table to store some customers
CREATE TABLE dbo.DONTEST_Customer --DROP TABLE DONTEST_Customer
(
CustomerID VarChar(10),
Name VarChar(10),
Address VarChar(10)
)
-- Here's a catalog reference to work with
CREATE TABLE dbo.DONTEST_Catalog --DROP TABLE DONTEST_Catalog
(
CatalogID VarChar(10),
CatalogName VarChar(10)
)

-- Here are the number of catalogs that each customer has requested
CREATE TABLE dbo.DONTEST_CatalogRequests --DROP TABLE DONTEST_CatalogRequests
(
CustomerID VarChar(10),
CatalogID VarChar(10),
NumberOfRequests TinyInt
)

-- Make an auxiliary numbers table...
-- this can be done using unions too, but this is easiest to understand
CREATE TABLE dbo.DONTEST_Numbers --DROP TABLE Numbers
(
NumberID TinyInt
)
-- Insert values into the numbers table
DECLARE @i TinyInt
SET @i = 1
WHILE @i < 20
BEGIN
INSERT INTO dbo.DONTEST_Numbers
VALUES(@i)
SET @i = @i + 1
END

-- Make some customers
INSERT INTO dbo.DONTEST_Customer
VALUES('asdf', 'Danny', '1234 Main')
INSERT INTO dbo.DONTEST_Customer
VALUES('fdsa', 'Denny', '4321 Main')
INSERT INTO dbo.DONTEST_Customer
VALUES('qwer', 'Donny', '4567 Main')
INSERT INTO dbo.DONTEST_Customer
VALUES('sdfg', 'Dinny', '6543 Main')
-- Make some catalogs
INSERT INTO dbo.DONTEST_Catalog
VALUES('zxcv', 'Summer')
INSERT INTO dbo.DONTEST_Catalog
VALUES('bvcx', 'Spring')
INSERT INTO dbo.DONTEST_Catalog
VALUES('lkjh', 'Winter')
INSERT INTO dbo.DONTEST_Catalog
VALUES('poiu', 'Fall')
-- Make some requests for catalogs with the number of catalogs that each
-- customer needs to receive
INSERT INTO dbo.DONTEST_CatalogRequests
VALUES('asdf', 'zxcv', 5)
INSERT INTO dbo.DONTEST_CatalogRequests
VALUES('asdf', 'lkjh', 2)
INSERT INTO dbo.DONTEST_CatalogRequests
VALUES('qwer', 'poiu', 4)
INSERT INTO dbo.DONTEST_CatalogRequests
VALUES('sdfg', 'bvcx', 3)
INSERT INTO dbo.DONTEST_CatalogRequests
VALUES('sdfg', 'lkjh', 6)
INSERT INTO dbo.DONTEST_CatalogRequests
VALUES('fdsa', 'lkjh', 10)
INSERT INTO dbo.DONTEST_CatalogRequests
VALUES('fdsa', 'poiu', 3)
-- Produce a resultset with the customer name and catalog name
SELECT Cus.Name, Cat.CatalogName, CR.NumberOfRequests
FROM dbo.DONTEST_Customer Cus
INNER JOIN dbo.DONTEST_CatalogRequests CR
ON Cus.CustomerID = CR.CustomerID
INNER JOIN dbo.DONTEST_Catalog Cat
ON CR.CatalogID = Cat.CatalogID
LEFT JOIN dbo.DONTEST_Numbers N
ON CR.NumberOfRequests >= N.NumberID

-- End Script

I hope this helps. Drop me a line if you need more information, or if you want to give me more data to play with, so that I can build this specifically to suit your particular situation.

--
Regards,
Don R. Watters
Data Group Manager
PhotoWorks, Inc.
DonW@Photoworks.com
0
DonW
8/2/2003 11:49:29 AM
Reply: