Select main id when one-to-many-table matches all criteria

SergioM · November 11, 2015, 9:58pm

I have one table which has a Product ID and some info. Then I have another table which has many product specs for each product. I would like to query the db such that it only returns the ID if one criteria is met but the other is not.

In the query below I am searching for a product with the word micro but not SDHC. The problem with my query is that the SDHC row is excluded, and the micro row is included and therefore the entire ID is returned. How would I properly format this query? In this case it should not return the ID at all because one of the rows is held within the list of traits which should be excluded.

IF OBJECT_ID('tempdb..#tmpSpecs') IS NOT NULL
   DROP TABLE #tmpSpecs
CREATE TABLE #tmpSpecs(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BHID] [int] NULL,
	[SpecCategory] [nvarchar](100) NULL,
	[SpecName] [nvarchar](100) NULL,
	[SpecValue] [nvarchar](500) NULL,
	[SpecGroup] [nvarchar](500) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT #tmpSpecs ON
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786128, 749518, NULL, N'Sensor', N'1x 5 Megapixel 1/3.2" &nbsp;CMOS Sensor', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786129, 749518, NULL, N'Lens', N'5.5 mm', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786130, 749518, NULL, N'Zoom', N'Digital: 4x', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786131, 749518, NULL, N'System', N'NTSC/PAL', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786132, 749518, NULL, N'Recording Media', N'SD/SDHC&nbsp;&nbsp;', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786133, 749518, NULL, N'Recording Time', N'<b>SD/SDHC GB</b><br>Up to 10 Hours', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786134, 749518, NULL, N'Video Format', N'<u><b>High Definition</b></u><br><b>MPEG4</b><br>1920 x 1080p / 30 fps<br>1280 x 720p / 60 fps<br>848 x 480p / 30 fps', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786135, 749518, NULL, N'Still Image Resolution', N'JPEG: 5.3 Megapixel', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786136, 749518, NULL, N'Channels', N'1.0-Channel Mono', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786137, 749518, NULL, N'Display Type', N'LCD', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786138, 749518, NULL, N'Touchscreen', N'No', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786139, 749518, NULL, N'Screen Size', N'2"', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786140, 749518, NULL, N'Image Stabilization', N'Digital', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786141, 749518, NULL, N'Built-in Mic', N'Yes', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786142, 749518, NULL, N'Built-in Speaker', N'Yes', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786143, 749518, NULL, N'Built-in Light/Flash', N'Light - No<br>Flash - No', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786144, 749518, NULL, N'Accessory Shoe', N'<i>None</i>', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786145, 749518, NULL, N'Tripod Mount', N'1/4"', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786146, 749518, NULL, N'Outputs', N'1x&nbsp;USB 2.0<br />1x&nbsp;A/V<br />1x&nbsp;HDMI D (Micro)', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786147, 749518, NULL, N'Microphone Input', N'No', NULL)
INSERT #tmpSpecs ([ID], [BHID], [SpecCategory], [SpecName], [SpecValue], [SpecGroup]) VALUES (1786148, 749518, NULL, N'Headphone Jack', N'No', NULL)
SET IDENTITY_INSERT #tmpSpecs OFF

SELECT BHID 
FROM  #tmpSpecs 
WHERE SpecValue LIKE '%micro%'
	AND SpecValue NOT LIKE '%SDHC%'

ScottPletcher · November 11, 2015, 10:09pm

SELECT BHID 
FROM #tmpSpecs 
GROUP BY BHID
HAVING MAX(CASE WHEN SpecValue LIKE '%micro%' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN SpecValue LIKE '%SDHC%' THEN 1 ELSE 0 END) = 0
--ORDER BY BHID

SergioM · November 16, 2015, 10:10pm

That's much more elegant than I expected. Needless to say it's also more efficient.

Thanks!

ScottPletcher · November 16, 2015, 10:31pm

You're welcome!

Note that it's flexible too: you can easily adjust it to add more "must match" and/or "must not match" options.

SergioM · November 18, 2015, 2:54pm

I did notice that. One of the other issues that I had is that I wanted to match "micro" but not "microphone". This setup allows for that to be easily added.

Thanks again