SQL Select when a grouped record has multiple matching strings

I have a large query, but my biggest problem lies in this small portion.

ItemID is foreign keyed to the Master Item table - it is not unique. This query doesn't do what I want (row can't have all 4 names) but it illustrates what I'm looking for.

Select masteritemid from itemsgrouptable
where itemname like 'Item 1' 
And itemname like 'Item 2' 
And itemname like 'Item 3' 
And itemname like 'Item 4'

I want to pull an itemid only if the开发者_Python百科 4 listed records exist with that ID. My current method joins the same table together 4 times based on ID, with each section looking at 1 string... it is HIGHLY inefficient.

Use a regex match:

select masteritemid from itemsgrouptable
where itemname regexp 'Item [1234].*'

This will match any itemname starting with either "Item 1" or "Item 2" or "Item 3" or "Item 4"

This comes up every once in a while. Not as straightforward as I would expect.

First, don't do a "LIKE" statement if it is an exact match. Like statements are less efficient than direct comparison.

Second, you can accomplish what you are asking in a GROUP BY / HAVING clause:

SELECT masteritemid FROM itemsgrouptable
GROUP BY masteritemid
HAVING COUNT(itemname) = 4
WHERE itemname IN ('Item 1','Item 2','Item 3','Item 4')

Basically you are grouping all of your items by the masteritemid, and restricting that grouped set where all 4 (or any number of "item #" matches) are within your specified "IN" clause. Kind of a neat trick.

TRY

SELECT masteritemid FROM itemsgrouptable
WHERE itemname REGEXP '^Item[:digit]?+' 

Reference