How to query item tags - 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)

How to query item tags

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2008-08-05 : 16:11:36

Hey everyone,

I'm building a document storage DB that allows people to tag documents they upload. Tags are stored in a separate table referencing the doc ID like so:

CREATE TABLE [dbo].[Cms_DocumentTags](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TagName] [nvarchar](50) NULL,
[DocId] [int] NOT NULL,
)

However, I'm trying to build a query to return documents that have match a set of tags (i.e. 'pdf' AND 'product'). Does anyone have a suggestion?

Thanks in advance!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-05 : 16:13:06

select * from Cms_DocumentTags a where a.TagName in ('Pdf','Product')

Is this what you're looking for??

Linok
Starting Member

34 Posts

Posted - 2008-08-05 : 16:47:46

It's close. That query seems to do an OR operation. I'm trying to get an AND.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-05 : 16:54:43

? what do you mean a and? are you saying that you want to pass PDF and Product and have it return something like 'MyProduct.pdf'?

if so you can use
select * from Cms_DocumentTags a where a.TagName like '%pdf%' and a.Tagname like '%product%'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-05 : 16:59:53

Here's another stab:

DECLARE @Cms_DocumentTags TABLE
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TagName] [nvarchar](50) NULL,
	[DocId] [int] NOT NULL
)

INSERT @Cms_DocumentTags (TagName, DocID)
SELECT 'pdf', 1
UNION ALL SELECT 'product', 1
UNION ALL SELECT 'doc', 2
UNION ALL SELECT 'non product', 2
UNION ALL SELECT 'pdf', 3
UNION ALL SELECT 'non product', 3
UNION ALL SELECT 'pdf', 4
UNION ALL SELECT 'product', 4
UNION ALL SELECT 'foo', 4


SELECT 
	DocID
FROM 
	@Cms_DocumentTags
WHERE
	TagName IN ('pdf', 'product')
GROUP BY
	DocID
HAVING
	COUNT(*) = 2

-- Or possibly AND ONLY..

SELECT
	T.DocID
FROM
	@Cms_DocumentTags AS T
INNER JOIN
	(
		SELECT 
			DocID
		FROM 
			@Cms_DocumentTags
		WHERE
			TagName IN ('pdf', 'product')
		GROUP BY
			DocID
		HAVING
			COUNT(*) = 2
	) AS A
	ON T.DocID = A.DocID
GROUP BY
	T.DocID
HAVING
	COUNT(*) = 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources