loop through

Question

Post reply

loop through

SalFernandez

SSC Enthusiast

Points: 192
More actions
February 26, 2004 at 11:21 am

#161512

Dear All,

sorry about the non-descriptive title...submitted before I had finishing typing...

I have a table MySupportFiles that holds file data.

I need to perform a search for files that uses several joins to include seaching related table such as file content and category.

These joins give me as many "instances" of the same file as any given file may have many different categories and so on. e.g. a given file has been categorized 5 times so due to joins this file now shows up 5 times in my result set.

Problem is once a file is located from the search criteria I only want to return that files data once, instead of the 5 records in our example.

I have used the script below to get me the unique file ID,s and am now attempting to use those to return me just one record per file from the MySupportFiles table.

I think I need some sort of looping to step through all the file ID's returned by the search but I'm not sure how I would do it.

Error I get in the code below is: msg 512 subquery returned more than 1 value

which is correct as there may be 1 up to 100, but how to loop through all the reurned values I do not know

Any ideas?

thanks in advance!

Sal

***************************************************
CREATE PROCEDURE GetUniqueID

AS
DECLARE @Search AS nvarchar(50)
DECLARE @CategoryID AS int

SET @Search = 'doc'
SET @CategoryID = 15

CREATE TABLE #TempResults
(
[FileID] [int] NOT NULL ,
[FileName] nvarchar (50),
FileSize nvarchar(50),
Extension nvarchar(50),
DateModified datetime,
DisplayDate datetime,
ShortDesc nvarchar(200),
LongDesc nvarchar(2000),
Platform nvarchar (50),
VersionID int,
FullVersionName nvarchar(100),
CategoryID int,
Product varchar(50),
ProductOld varchar(50)
)

INSERT INTO #TempResults (FileID, [FileName], FileSize, Extension, DateModified, DisplayDate, ShortDesc, LongDesc, Platform, VersionID, FullVersionName, CategoryID, Product, ProductOld)

SELECT
            dbo.MySupportFiles.FileID,
            dbo.MySupportFiles.FileName,
            dbo.MySupportFileContent.FileSize,
            dbo.MySupportFileContent.Extension,
            dbo.MySupportFiles.DateModified,
            dbo.MySupportFiles.DisplayDate,
            dbo.MySupportFiles.ShortDesc,
            dbo.MySupportFiles.LongDescription,
            dbo.MySupportFiles.Platform,
            dbo.MySupportFiles.VersionID,
            CAST(product_version.prod_major AS varchar) + '.' + CAST(product_version.prod_minor AS varchar) + '.' + product_version.prod_build + ' SP' + product_version.prod_sp_build AS FullVersionName,
            dbo.MySupportFileCategory.CategoryID,
            dbo.product.prod_descr AS Product,
            dbo.product.prod_old AS ProductOld
            FROM
                                    dbo.MySupportFiles
                                    INNER JOIN dbo.MySupportFileContent ON dbo.MySupportFiles.FileID = dbo.MySupportFileContent.FileID
                                    INNER JOIN dbo.MySupportFileCategory ON dbo.MySupportFiles.FileID = dbo.MySupportFileCategory.FileID
                                    INNER JOIN dbo.MySupportCategories ON dbo.MySupportFileCategory.CategoryID = dbo.MySupportCategories.CategoryID
                                    INNER JOIN dbo.product ON dbo.MySupportFiles.ProductID = dbo.product.prod_urn
                                    LEFT OUTER JOIN product_version ON dbo.MySupportFiles.ProductID = dbo.product_version.prod_urn
            WHERE
                                    (FileName LIKE N'%' + @Search + '%') OR
                                    (LongDescription LIKE N'%' + @Search + '%') OR
                                              (ShortDesc LIKE N'%' + @Search + '%') OR
                                              (Platform LIKE N'%' + @Search + '%') OR
                                    (dbo.MySupportCategories.CategoryDesc LIKE N'%' + @Search + '%') OR
                                             (dbo.MySupportCategories.CategoryName LIKE N'%' + @Search + '%')

                                    AND dbo.MySupportFiles.Visible = 1 AND MySupportCategories.CategoryID = @CategoryID

SELECT * FROM dbo.MySupportFiles WHERE FileID = (SELECT DISTINCT FileID from #TempResults) --select * used while testing

drop table #TempResults

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

I am not sure of your table structure or relationships there but this is a shot:

SELECT DISTINCT

dbo.MySupportFiles.FileID,

dbo.MySupportFiles.FileName,

dbo.MySupportFileContent.FileSize,

dbo.MySupportFileContent.Extension,

dbo.MySupportFiles.DateModified,

dbo.MySupportFiles.DisplayDate,

dbo.MySupportFiles.ShortDesc,

dbo.MySupportFiles.LongDescription,

dbo.MySupportFiles.Platform,

dbo.MySupportFiles.VersionID,

CAST(product_version.prod_major AS varchar) + '.' + CAST(product_version.prod_minor AS varchar) + '.' + product_version.prod_build + ' SP' + product_version.prod_sp_build AS FullVersionName,

dbo.MySupportFileCategory.CategoryID,

dbo.product.prod_descr AS Product,

dbo.product.prod_old AS ProductOld

FROM

dbo.MySupportFiles

INNER JOIN dbo.MySupportFileContent ON dbo.MySupportFiles.FileID = dbo.MySupportFileContent.FileID

INNER JOIN dbo.MySupportFileCategory ON dbo.MySupportFiles.FileID = dbo.MySupportFileCategory.FileID

INNER JOIN dbo.MySupportCategories ON dbo.MySupportFileCategory.CategoryID = dbo.MySupportCategories.CategoryID

INNER JOIN dbo.product ON dbo.MySupportFiles.ProductID = dbo.product.prod_urn

LEFT OUTER JOIN product_version ON dbo.MySupportFiles.ProductID = dbo.product_version.prod_urn

WHERE

(FileName LIKE N'%' + @Search + '%') OR

(LongDescription LIKE N'%' + @Search + '%') OR

(ShortDesc LIKE N'%' + @Search + '%') OR

(Platform LIKE N'%' + @Search + '%') OR

(dbo.MySupportCategories.CategoryDesc LIKE N'%' + @Search + '%') OR

(dbo.MySupportCategories.CategoryName LIKE N'%' + @Search + '%')

AND dbo.MySupportFiles.Visible = 1 AND MySupportCategories.CategoryID = @CategoryID

* Noel

SalFernandez SSC Enthusiast Points: 192 More actions · Answer 2

Thanks but that won't do it...

this is another way to describe my scenario:

file table

FileID	FileName
7	xfiles
8	secret

category table

CatID	CatName
2	cats
3	dogs
4	fish
5	fishandchips

File Category Relationship table:

CatID	FileID
2	7
3	7
4	7
4	8
5	8

Join for searching will produce:

CatID	FileID	CatName	FileName
2	7	cats	xfiles
3	7	dogs	xfiles
4	7	fish	xfiles
4	8	fish	secret
5	8	fishandchips	secret

if I search for the word fish I will get this:

4	7	fish	xfiles
4	8	fish	secret
5	8	fishandchips	secret

but what I want is this

FileID	FileName
7	xfiles
8	secret

I don't want fileID 8 in there 2 times.

I want any file that is a match to appear on my returned set only once (even if it had matched more than once)

I hope someone can help me out on this!

TIA

SAL

noeld SSC Guru Points: 96590 More actions · Answer 3

if all you want is FileID and File name as the output then

Select DISTINCT Q.FileId, Q.Filename

From

( Your Query) Q

order by Q.FileID, Q.FileName

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 4

If you need ALL the Fields then here it goes :

SELECT T.*

FROM

#TempResults T

Join

( Select DISTINCT Q.FileId, Q.Filename

From #TempResults) Q

On Q.FileId =T.FileId and Q.Filename = T.FileName

order by T.FileID, T.FileName

* Noel

SalFernandez SSC Enthusiast Points: 192 More actions · Answer 5

SalFernandez

SSC Enthusiast

Points: 192

February 29, 2004 at 12:55 am

#496361

excellent, exactly what I needed!

thanks noeld!