subset with multiple criteria

[SunWuKung]

I have an item pool from which I draw items to create Instruments. Each item has a ContentID. In an instrument items can be scored differently so I have a ScoringTypeID. This makes up a table like:

InstrumentHeaderID
ContentID
ScoringTypeID

InstrumentHeaderID and ContentID togather uniquely identifies the record.

My task is that when I specify an Instrument I must find all Instuments that contain every item that that the supplied instrument contained with the items being scored the same way.
In other words I must return all Instruments that has all the items of the supplied Instrument scored in the same way.

I was unable to do this.
Could somebody help me?

 

[llkhoutx]

I don't quite understand you tables, but hink that you should try a right join in a query on ContentID.

Then maybe another query on that query right joining on the table with ScoringTypeID.

 

[SunWuKung]

I finally made it.
The trick was to check the Count of the common part.

SELECT MeasureInst.InstrumentHeaderID, @ReportInstrumentHeaderID
FROM InstrumentDetailScorableItems ReportInst INNER JOIN
InstrumentDetailScorableItems MeasureInst ON ReportInst.ComponentContentID = MeasureInst.ComponentContentID AND
ReportInst.ScoringTypeID = MeasureInst.ScoringTypeID
WHERE (ReportInst.InstrumentHeaderID = @ReportInstrumentHeaderID)
GROUP BY MeasureInst.InstrumentHeaderID
HAVING (COUNT(ReportInst.ComponentContentID) =
(SELECT COUNT(ComponentContentID)
FROM InstrumentDetailScorableItems
WHERE InstrumentHeaderID = @ReportInstrumentHeaderID))