What about using UNION?
select count(*) from
(
SELECT * as Total FROM Cycles WHERE BrandNameID1_1=#LoopIndex#)
union
SELECT * as Total FROM Cycles WHERE BrandNameID2_1=#LoopIndex#)
union
.
.
.
Steve
-------------------------------------
Steven Monaghan
Oracle DBA
MSC Industrial Direct Co., Inc.
Melville, NY
[EMAIL PROTECTED]
http://www.mscdirect.com
-------------------------------------
-----Original Message-----
From: Brian Ferrigno [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 10, 2001 11:52 AM
To: CF-Talk
Subject: SQL help for a brain that's not awake yet
I know the answer to my problem is easy I just can't seem to get my brain
functioning today.
My problem is that I want to get the number of times an integer ID appears
in a list of columns for every row in a table. The integer ID is from an
outer SQL statement that I am looping through.
An example
Current ID = 1
DBColumn 1 DBColumn 2
-------- --------
1 1
2 1
1 1
The result should be 5. The second time I loop through the outer sql
statement the ID would be 2 and the result should = 1
Here is what the current SQL statement looks like:
SELECT Count(*) as Total FROM Cycles WHERE (BrandNameID1_1=#LoopIndex# OR
BrandNameID2_1=#LoopIndex# OR BrandNameID3_1=#LoopIndex# OR
BrandNameID1_2=#LoopIndex# OR BrandNameID2_2=#LoopIndex# OR
BrandNameID3_2=#LoopIndex# OR BrandNameID1_3=#LoopIndex# OR
BrandNameID2_3=#LoopIndex# OR BrandNameID3_3=#LoopIndex#)
This however returns only the number of rows that have the loop index in it.
I know there is probably an easy solution to this but for the life of me I
can't think straight today.
Any help would be greatly appreciated.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
