October 26, 2015 at 2:31 pm
This is the database structure I setup:
--Main Table
CREATE TABLE [dbo].[tbl_RF_Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ProgramID] [tinyint] NOT NULL,
[StatusID] [tinyint] NOT NULL,
[Item] [nvarchar](256) NOT NULL,
)
--Mapping Table
CREATE TABLE [dbo].[tbl_RF_Tags_Map_Items](
[TagID] [int] NOT NULL,
[ItemID] [int] NOT NULL,
[DateModified] [smalldatetime] NULL,
CONSTRAINT [PK_tbl_RF_Tags_Item_Map] PRIMARY KEY CLUSTERED
(
[TagID] ASC,
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This a result set of the Items Map table so far:
TagIDItemID
1284838
1291475
1291480
8284838
8291475
8291480
10284838
10291480
62291475
Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.
It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
October 26, 2015 at 2:37 pm
pmb88 (10/26/2015)
I have a client who wants to start using tags with their items. I am having trouble on how to filter the data. For example I want only the items that have TagID 10,15, and 25. IN does not work cause that functions like OR and I need to function like AND. It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
Hi and welcome to SSC. Yes there are other options. The challenge is that it is very difficult to offer suggestions because there isn't any detail provided here. Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2015 at 3:04 pm
In general, you can use an approach like below to efficiently determine if specific, multiple tags match for the same item. Naturally we'd need more details to get any more specific.
SELECT item_id
FROM table_name
GROUP BY item_id
HAVING MAX(CASE WHEN TagID = 10 THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN TagID = 15 THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN TagID = 25 THEN 1 ELSE 0 END) = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2015 at 3:14 pm
Scott I edited my question.
October 26, 2015 at 3:50 pm
SELECT rtmi.ItemID
FROM tbl_RF_Tags_Map_Items rtmi
WHERE rtmi.TagID IN (1, 8, 62)
GROUP BY rtmi.ItemID
HAVING MAX(CASE WHEN rtmi.TagID = 1 THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rtmi.TagID = 8 THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rtmi.TagID = 62 THEN 1 ELSE 0 END) = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2015 at 8:42 am
Scott, the issue I have is with the HAVING clause. The values selected may change so that would not work in all instances. However, your answer has given me an idea how to solve my problem. Change the HAVING clause to count item id and compare to the count of tags selected. If it matches, then it has all the tags required.
DECLARE @TagCount int
DECLARE @Tags varchar(max)='1, 8, 62'
DECLARE @SelectedTags table(
TagID int
)
INSERT INTO @SelectedTags (Token)
SELECT TagID from dbo.fn_RF_SplitString(@Tags,',');
SELECT @TagCount = COUNT(TagID)
FROM @SelectedTags;
SELECT rtmi.ItemID
FROM tbl_RF_Tags_Map_Items rtmi
WHERE rtmi.TagID IN (SELECT TagID FROM @SelectedTags)
GROUP BY rtmi.ItemID
HAVING COUNT(Distinct rtmi.ItemID) = @TagCount
October 27, 2015 at 9:39 am
This type of problem has a name - Relational Division - and a number of solutions. This is the simplest solution:
CREATE TABLE #ItemsMap (TagID INT, ItemID INT)
INSERT INTO #ItemsMap (TagID,ItemID) VALUES
(1,284838), (1,291475), (1,291480), (8,284838), (8,291475), (8,291480), (10,284838), (10,291480), (62,291475)
CREATE TABLE #Probe (TagID INT)
INSERT INTO #Probe (TagID) VALUES (1),(8),(62)
-- Relational division
SELECT im.ItemID, COUNT(im.TagID)
FROM #ItemsMap im
INNER JOIN #Probe p ON p.TagID = im.TagID
GROUP BY im.ItemID
HAVING COUNT(*) = (SELECT COUNT(*) FROM #Probe)
Google "Relational Division" for more sophisticated solutions - why reinvent the wheel?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply