SQL Select when a grouped record has multiple matching rows

Question

I have a big request, but my biggest problem is this small part.

ItemID is external, attached to the table of main positions - it is not unique. This query does not do what I want (the string cannot 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 out the itemid only if there are 4 listed entries with this ID. My current method combines the same table 4 times depending on the identifier, with each section looking at 1 row ... it is HIGHLY inefficient.

Answer 1

Use regex:

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

This will match any item name starting with "Item 1" or "Item 2" or "Item 3" or "Item 4"

Answer 2

It happens every time. Not as straightforward as I expected.

First, don’t do the “LIKE” statement if it is an exact match. Such statements are less effective than direct comparison.

Secondly, you can accomplish what you request in the 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 group all your objects with masteritemid and limit this grouped set where all 4 (or any number of "item #" matches) are within your specified "IN" clause. Kind of a neat trick.

Answer 3

TRY

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

Link