CODEWITHSUNDEEP
mysqlsql
Neil M.
Neil M.

Reputation: 456

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 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.

Upvotes: 4

Views: 332

Answers (3)

xkeshav
xkeshav

Reputation: 54022

TRY

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

Reference

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

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"

Upvotes: 3

feathj
feathj

Reputation: 3069

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.

Upvotes: 1

Related Questions