August 27, 2008 at 1:03 pm
I haven't quite been able to get my hands around this query this afternoon. I have 3 tables. A formula table, a criteria table and an intersection table between the two, Formula_Criteria.
table defs
CREATE TABLE [Criteria] (
[Criteria_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Criteria] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Criteria] PRIMARY KEY CLUSTERED
(
[Criteria_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT CRITERIA ON
INSERT INTO CRITERIA (CRITERIA_ID, CRITERIA)
SELECT '1','SPF101' UNION ALL
SELECT '2','EXT345' UNION ALL
SELECT '3','STF685' UNION ALL
SELECT '4','SHT567' UNION ALL
SELECT '5','RSS327' UNION ALL
SELECT '6','RSE679' UNION ALL
SELECT '7','HEY987' UNION ALL
SELECT '8','STE332' UNION ALL
SELECT '9','MMG550'
SET IDENTITY_INSERT CRITERIA OFF
CREATE TABLE [Formulas] (
[Formula_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Formula_Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Formulas] PRIMARY KEY CLUSTERED
(
[Formula_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT FORMULAS ON
INSERT INTO FORMULAS (FORMULA_ID, FORMULA_NAME)
SELECT '1','MFE136' UNION ALL
SELECT '2','MFD438' UNION ALL
SELECT '3','MFE5635' UNION ALL
SELECT '4','MRE1062' UNION ALL
SELECT '5','MTGRS124' UNION ALL
SELECT '6','MBF289' UNION ALL
SELECT '7','MRF260' UNION ALL
SELECT '8','MTR2545' UNION ALL
SELECT '9','MWE2435'
SET IDENTITY_INSERT FORMULAS OFF
CREATE TABLE [Formula_Criteria] (
[Formula_ID] [int] NOT NULL ,
[Criteria_ID] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO FORMULA_CRITERIA (FORMULA_ID, Criteria_ID)
SELECT '1','2' UNION ALL
SELECT '1','8' UNION ALL
SELECT '2','2' UNION ALL
SELECT '2','1' UNION ALL
SELECT '2','3' UNION ALL
SELECT '2','8' UNION ALL
SELECT '3','5' UNION ALL
SELECT '3','1' UNION ALL
SELECT '3','8' UNION ALL
SELECT '4','2' UNION ALL
SELECT '4','9'
What I want to find is for any given set of criteria, I want to know all the formuals that have at least all those criteria in the intersect table. Given the above data, when I have criteria_ID's of 2, 8, I should get formula_id 1,2 (mfe136, mfd438).
Here is what I have thus far. I can get the unique number of criteria in code for the count but I would like to bypass that. The IN acts just like an OR so I get any formula with any combination of the two. They can one or the other or both, I just want the ones with both.
select fm.formula_name
from formulas fm
inner join formula_criteria fc on fc.formula_id = fm.formula_id
inner join criteria cr on cr.criteria_id = fc.criteria_id
and cr.criteria_id in( 2, 8)
group by fm.formula_name
having count(*) = 2
order by fm.formula_name
Thanks
Allen
Thanks rbarryyoung - I flipped them around.
Code fixed for data creation
Thanks
August 27, 2008 at 1:39 pm
FYI, you got your Set IDENTITY_INSERT On/Offs backwards.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 7:11 am
Before posting your SQL on the forum, pls at least check if it runs correctly, and with this increase your chance getting the question answered.
Going back to the problem, have you tried sth like this:
SELECT fm.formula_id
FROM formulas fm
INNER JOIN formula_criteria fc1
ON fc1.formula_id = fm.formula_id
AND fc1.criteria_id = 2
INNER JOIN formula_criteria fc2
ON fc2.formula_id = fm.formula_id
AND fc2.criteria_id = 8
August 28, 2008 at 7:22 am
I apologize for the errors.
This works but the number of criteria that could be selected could be a dozen or more. I won't know before hand. I was hoping there was a way to get the answer without building dynamic sql.
Thanks
August 28, 2008 at 7:39 am
What about using temp table to store criteria IDs, of course you can replace UNION ALL statement with function that will transform coma delimited string with criteria IDs to rowset:
DECLARE @Count Int
IF OBJECT_ID('tempdb..#crt') IS NOT NULL
DROP TABLE #crt
SELECT * INTO #crt
FROM (
SELECT 2 Criteria_Id
UNION ALL
SELECT 8
) x
SELECT @Count = Count(*) FROM #crt
SELECT fm.formula_id
FROM formulas fm
INNER JOIN formula_criteria fc1
ON fc1.formula_id = fm.formula_id
INNER JOIN #crt crt
ON fc1.criteria_id = crt.Criteria_Id
GROUP BY fm.formula_id
HAVING Count(*) = @Count
August 28, 2008 at 7:46 am
That look like that would work, and since the same criteria could be selected more than once, I can do distinct count on that temp table.
Thanks
for your help Max
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply