Tough Query ! - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Tough Query !

Author

Topic

b0ss
Starting Member

4 Posts

Posted - 2009-03-27 : 10:58:32

Hi there.
I'm developing a document archive app and I'm planning to put some search tools on it.

Let's say user will search for a document and will select some departments from a list box (can be multiple choice or none). He/she will write down subject of document in a text box etc. from the page.

Here is the rough schema of db design.



Documents                            
---------                            
DocID   DocSubject                           
-----   ----------
 1      Sub 1                                      
 2      Sub 2                                      
 3      Sub 3                                      

DocBuilderDeps (link table)
--------------------------
DocID       DepID
-----      -------
 1          2
 1          3
 2          1

Deparments
--------------
DepID   DepName
------  --------
 1    Accounting
 2    Sales
 3    Some dep

Currently I'm building a sql string and executing it with sp_executesql. But I have trouble with building the query. What I'm tyring to do is that when user enter a subject and choose one or many or none of departments query will look for documents where subject is like provided one and the document has been prepared by selected departments(with AND not OR).

The latter search will take place in DocBuilderDeps table but comparison must be done vertically with AND.

For example it may be looking for document where both department 2 and department 3 prepared together where document with ID 1 is the record. In some cases there won't be any search criteria for department at all.

It is a little confusing because of me, but I'm sure there is a solution for this kind of problem. I'm asking for suggestions for this problem.

Thanks for helping.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-27 : 11:21:10

what are the required fields when doing the search? all or none?

b0ss
Starting Member

4 Posts

Posted - 2009-03-27 : 11:25:44

They are optional. User may provide all of them, or none of them it that case all document records will return.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-27 : 11:35:28

is your query department centric? do you want your query to return departments that have not worked on documents?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 11:44:14

Similar to this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=122245

Build a string of DocId and depid

SELECT Documents.*
FROM Documents INNER JOIN
DocBuilderDeps ON Documents.DocID = DocBuilderDeps.DocID
INNER JOIN Deparments ON DocBuilderDeps.DepID = Deparments.DepID
where DocID in
(SELECT Item FROM split(@id, ','))
and DepID
in
(SELECT Item FROM split(@Depid, ','))

b0ss
Starting Member

4 Posts

Posted - 2009-03-27 : 11:48:29

Department is look up table. Document is at the center.
There is a chance that a document may be prepared by many departments and a department may prepare many documents, thus I build a linking table for many-to-many relation between documents and departments.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-27 : 11:49:37

Here's a sample, all you need to do is pass the variable as illustrated


drop table #TDoc
drop table #TDocBuilderDeps
drop table #TDepartments
Create table #TDoc (DocID int,   DocSubject varchar(30))
Insert into #Tdoc(DocID,DocSubject)
select 1,      'Sub 1' Union All                                     
select 2,      'Sub 2' Union All                                     
select 3,      'Sub 3'                                      


Create table #TDocBuilderDeps (DocID int,DepID int)
Insert Into #TdocBuilderDeps
 select 1,          2 Union All
 select 1,          3 Union All
 select 2,          1

Create table  #TDepartments (DepID int, DepName varchar(30))
Insert Into #TDepartments(DepID,DepName)
SELECT 1,    'Accounting' Union all
SELECT 2,    'Sales' Union all
SELECT 3,    'Some dep'

declare @DocSubject varchar(100),@Departments varchar(100)
set @DocSubject = 'ub 2'
set @Departments = '''Accounting'',''Sales'''
exec ('
Select distinct a.*,c.DepName
from 
#Tdoc a
Inner Join
#TdocBuilderDeps b
on a.DocID = b.DocID
Inner Join
#TDepartments c
on b.DepID = c.DepID
where
a.DocSubject like ''%'+ @DocSubject + '%''
and
c.DepName in ('+ @Departments + ')')

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-27 : 11:54:31

or you can do it like follows per: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=122581

DECLARE @SqlString NVARCHAR(MAX)
DECLARE @Docs VARCHAR(MAX)
DECLARE @DocBuilderDeps VARCHAR(MAX)
DECLARE @Departments VARCHAR(MAX)
--DocBuilderDeps
--Departments
--Docs

SET @Docs = ''
SEt @DocBuilderDeps = ''
SET @Departments = ''

SET @SqlString = 'SELECT *
FROM Departments dpt
LEFT OUTER JOIN DocBuilderDeps dbp
ON dpt.DepID = dbp.DepID
LEFT OUTER JOIN documents d
ON dbp.DocID = d.DocID
WHERE Docsubject = @Docs '
so for example you can pass
EXEC sp_executesql @SqlString, N'@Docs varchar(50), @DocBuilderDeps varchar(50), @Departments varchar(50)', 'Sub 2', '', ''

If you don't have the passion to help people, you have no passion

b0ss
Starting Member

4 Posts

Posted - 2009-03-27 : 12:06:47

I'll try these. Thanks fellows. I guest darkdusky's solution may work, because I have to put those department parameters in where clause somehow.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-27 : 12:10:36

here it is with WHERe clause solution

DECLARE @SqlString NVARCHAR(MAX)
DECLARE @Docs VARCHAR(MAX)
DECLARE @DocBuilderDeps VARCHAR(MAX)
DECLARE @Departments VARCHAR(MAX)
--DocBuilderDeps
--Departments
--Docs

--SET @Docs = 'Sub 2'
--SET @DocBuilderDeps = ''
SET @Departments = 'Accounting'
--Accounting
--Sales
--Some dep
SET @SqlString = 'SELECT *
FROM Departments dpt
LEFT OUTER JOIN DocBuilderDeps dbp
ON dpt.DepID = dbp.DepID
LEFT OUTER JOIN documents d
ON dbp.DocID = d.DocID
WHERE 1 = 1 '

IF @Docs IS NOT NULL
SELECT @SqlString = @SqlString + ' AND DocSubject LIKE ''' + @Docs + ''''

IF @Departments IS NOT NULL
SELECT @SqlString = @SqlString + ' AND DepName LIKE ''' + @Departments + ''''

--PRINT @SqlString

EXEC(@SqlString)

EXEC sp_executesql @SqlString, N'@Docs varchar(50), @Departments varchar(50)',@Docs, @Departments

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources