Help with SELECT 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 2000 Forums

Transact-SQL (2000)

Help with SELECT query

Author

Topic

bmango
Starting Member

12 Posts

Posted - 2009-03-09 : 07:22:55

Hi there,

I want to filter a table based on multiple values in the same column.

I have a joining table called lpp_doccats with 3 columns, an autoid column, document id and category id (id, docid, catid). The table shows which documents belong to which categories. So one document can belong to more than one category but this is achieved using multiple rows - one row for each of the categories the document belongs to.

How can I find those documents that have 2 or more specific categories? I will need up to 5 possible categories for each document.

Some sample data:

1 1 1
2 1 2
3 1 3
4 2 2
5 2 3
6 3 1
7 3 3
8 3 4
9 4 2
10 4 1

So, for instance, those documents (second column) that have both categories 1 and 2 (third column) would be document 1 and document 4.

I get the values from an ASP page with 5 drop down lists for each of the categories - so I will know the category ids in advance - and then build the query on the fly.

I already posted this question in the 2005 forum by mistake. One of the solutions was:

select t.docid from lpp_doccats t join lpp_doccats t1 on t.docid = t1.docid and t.catid = 1 and t1.catid = 2

However, this solution gets quite complex with 5 seperate categories.

Any help, much appreciated.

Ben

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-03-09 : 08:18:18

try the below one. WHere You have to provide the value of @CatIds as in comma seperated format of your category ids. Suppose you need the documents in the categories 1,2,3 then pass the value 1,2,3

declare @CatIds varchar(50)
set @CatIds = '1,2,3'
declare @NoOfCategories int
set @NoOfCategories = len(@CatIds) - len(replace(@CatIds,',','')) + 1
declare @str nvarchar(max)
set @str = 'select * from ( select distinct DocId,(select count(Distinct CatId) from Documents where DocId = D.DocId and CatId in ('+ @CatIds + ') ) as Cnt from Documents D)t where cnt >= ' + cast( @NoOfCategories as varchar)
exec sp_executesql @str

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 08:34:16

hi bmango
can u post some sample output .

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-09 : 08:45:37

[code]select docid from lpp_doccats
where catid in (1,2)--parametrize this if needed
group by docid
having count(distinct catid)>1[/code]

bmango
Starting Member

12 Posts

Posted - 2009-03-09 : 09:18:14

Hi sakets_2000

thank you very much. that's a really simple solution. can you explain to me how the last line works?

ben

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-09 : 09:26:26

[code]select docid from lpp_doccats
where catid in (1,2)--this pulls just catid 1 and 2
group by docid
having count(distinct catid)>1-- counts number of distinct catid per docid, keeps those which have more than one and leaves the rest[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources