How to match all items in a csv list

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

General SQL Server Forums

New to SQL Server Programming

How to match all items in a csv list

Author

Topic

edsilv
Starting Member

9 Posts

Posted - 2009-06-08 : 06:25:29

Hi,

I need to return Instances (pages in a CMS) wherever they match a list of ALL tags. This is what I'm currently doing:

SELECT DISTINCT InstanceId FROM cms_InstanceTags
WHERE TagId IN
(
SELECT TagId FROM cms_Tags
WHERE Name IN (@Tags)
)

There's a 2-column mapping table called cms_InstanceTags that maps instance id's to tag id's.
I'm replacing @Tags with (for example) 'Tag1','Tag2'...

This works currently but the problem is I'm getting instance id's returned that match ANY of the tags not ALL of the tags. I've tried using the ALL keyword:

WHERE TagId = ALL
(
...
)

But I think I'm misunderstanding it's use as this returns the same results.

Ed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 06:39:48

We need to see your table layout.

E 12°55'05.63"
N 56°04'39.26"

edsilv
Starting Member

9 Posts

Posted - 2009-06-08 : 06:43:07

CREATE TABLE [dbo].[cms_Tags](
[TagId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL
}

CREATE TABLE [dbo].[cms_InstanceTags](
[TagId] [int] NOT NULL,
[InstanceId] [int] NOT NULL
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 06:46:29

You need to do a GROUP BY of some sort.
A column value can't have two values at the same time.
So the ALL (1, 2) is not valid. A column value for any one record can't be both 1 AND 2.

E 12°55'05.63"
N 56°04'39.26"

edsilv
Starting Member

9 Posts

Posted - 2009-06-08 : 06:59:44

I've been looking at GROUP BY but can't understand specifically how I'd use it in this context.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 07:01:57

Provide some sample data to your tables and we'l give it a spin.

E 12°55'05.63"
N 56°04'39.26"

edsilv
Starting Member

9 Posts

Posted - 2009-06-08 : 07:05:26

cms_Tags table contents:

TagId, Name, CreatedDate
8, Top Story, 2009-05-16 23:02:12.047
10, Press Release, 2009-05-17 01:03:12.500
22, Fashion, 2009-05-19 13:34:25.023
23, Landscape, 2009-05-19 13:34:30.163
24, Portrait, 2009-05-19 13:34:35.023

cms_InstanceTags table contents:

TagId, InstanceId
10, 7
8, 7
8, 9
10, 9

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 07:13:22

[code]DECLARE @Tags TABLE
(
TagID INT IDENTITY(1, 1) NOT NULL,
Name VARCHAR(50) NOT NULL,
CreatedDate DATETIME NOT NULL
)

INSERT @Tags
SELECT 8, 'Top Story', '2009-05-16 23:02:12.047' UNION ALL
SELECT 10, 'Press Release', '2009-05-17 01:03:12.500' UNION ALL
SELECT 22, 'Fashion', '2009-05-19 13:34:25.023' UNION ALL
SELECT 23, 'Landscape', '2009-05-19 13:34:30.163' UNION ALL
SELECT 24, 'Portrait', '2009-05-19 13:34:35.023'

DECLARE @InstanceTags
(
TagID INT NOT NULL,
InstanceID INT NOT NULL
)

INSERT @InstanceTags
SELECT 10, 7 UNION ALL
SELECT 8, 7 UNION ALL
SELECT 8, 9 UNION ALL
SELECT 10, 6

SELECT t.*
FROM @Tags AS t
INNER JOIN (
SELECT it.TagID
FROM @InstanceTags AS it
INNER JOIN dbo.fnParseList(',', '7,9') AS p ON p.Data = it.InstanceID
GROUP BY it.TagID
HAVING COUNT(*) = 2
) AS d ON d.TagID = t.TagID[/code]

E 12°55'05.63"
N 56°04'39.26"

edsilv
Starting Member

9 Posts

Posted - 2009-06-08 : 07:32:47

I think there's been a misunderstanding. I've tried that and it returns a list of tags for the instance id's 7 and 9. But what I'm trying to do is get instance id's that correspond to ALL tag id's. So I'd say "get me all the instance id's that have the tags 'Press Release' AND 'Top Story'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 07:41:46

[code]DECLARE @Tags TABLE
(
TagID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
CreatedDate DATETIME NOT NULL
)

INSERT @Tags
SELECT 8, 'Top Story', '2009-05-16 23:02:12.047' UNION ALL
SELECT 10, 'Press Release', '2009-05-17 01:03:12.500' UNION ALL
SELECT 22, 'Fashion', '2009-05-19 13:34:25.023' UNION ALL
SELECT 23, 'Landscape', '2009-05-19 13:34:30.163' UNION ALL
SELECT 24, 'Portrait', '2009-05-19 13:34:35.023'

DECLARE @InstanceTags TABLE
(
TagID INT NOT NULL,
InstanceID INT NOT NULL
)

INSERT @InstanceTags
SELECT 10, 7 UNION ALL
SELECT 8, 7 UNION ALL
SELECT 8, 9 UNION ALL
SELECT 10, 8

SELECT it.InstanceID
FROM @Tags AS t
INNER JOIN @InstanceTags AS it ON it.TagID = t.TagID
WHERE t.Name IN ('Top story', 'Press release')
GROUP BY it.InstanceID
HAVING COUNT(DISTINCT t.Name) = 2[/code]

E 12°55'05.63"
N 56°04'39.26"

edsilv
Starting Member

9 Posts

Posted - 2009-06-08 : 07:49:34

That seems to be perfect. Many thanks :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources