linking Select results from different databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

linking Select results from different databases

Author

Topic

C4ppy
Starting Member

1 Post

Posted - 2002-03-08 : 07:11:31

Hi..

On a site I am developing we are hosting a pdf library. I have index server running on this library so I can search for required pdf files.

All information relating to each file (ie. author, subject, etc) is stored in a database linked by the files name.

Now when i search the index of pdfs, I get a list of relevant filenames.

I then use this list of filenames to generate an SQL statement which looks like this.

select * from pdfinfo where filename="file1.pdf" or filename="file2.pdf" or filename="file3.pdf" or .....

Index server produces a ranked list of filenames so the files are ordered in terms of relevance.

The second SQL statement that selects the pdf orders the list in the order they are in the database. Is there a way to maintain the order in which the files are listed in the where statement.. or is there a better way of doing this?

Thanks
Amit

Jay99

468 Posts

Posted - 2002-03-08 : 09:55:22

Yuck . . .

First, with SQL 2K you can query Index Server directly. Don't know much about it, but maybe you should look into it for you solution.

Two (bad) options come to mind on this.


select 1 as ordercol, *
from pdfinfo
where filename = 'file1.pdf'
union
select 2 as ordercol, *
from pdfinfo
where filename = 'file1.pdf'
union
...
union
select N as ordercol, *
from pdfinfo
where filename = 'fileN.pdf'
order by ordercol


select
    case filename
       when 'file1.pdf' then 1
       when 'file2.pdf' then 2
       ...
       when 'fileN.pdf' then N
    end as ordercol,
   *
from pdfinfo
where filename in ('file1.pdf','file2.pdf'...'fileN.pdf')

Both are lame...

Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources