CODEWITHSUNDEEP
sql-serversql-server-2016
Learn
Learn

Reputation: 548

SQL select rows based on wildcards and = in IN Clause

I have a situation where I need to select Customer_IDs whose items that contain the keyword "box" and "phone", which mean, the minimum condition is at least 2 rows, one for box, one for phone, both need to show up for that customer_ID, order doesn't matter, but the challenge is for example, phone3105557890, the sting part is always the same, "phone", but the final part vary,

sample data

    Customer_ID     item             year
1   222             box              2018
2   222         phone3105557890      2018
3   222             box              2017
4   444             box              2018
5   444             pen              2018
6   444            apple             2018
7   666             table            2018
8   666             box              2018
9   666         phone9995467777      2018

expected result

    Customer_ID     item             year
1   222             box              2018
2   222         phone3105557890      2018
3   222             box              2017
4   666             table            2018
5   666             box              2018
6   666         phone9995467777      2018

Warning: using select customer_ID from myTable where item = 'box' or item like 'phone%' can't give me what I want coz I need both box and phone rows both show up as a minimum requirement. that's why 444 will be filtered out.

my attempt:

select customer_ID from myTable where item IN ('box','phone')

but phone need a wildcard, where item LIKE 'phone%'

how to combine two above logic to make it work? do I need to declare a variable?

Upvotes: 2

Views: 122

Answers (2)

S3S
S3S

Reputation: 25142

You can use IN and HAVING

select *
from myTable
where customer_ID in (select customer_ID 
                      from myTable 
                      where item = 'box' or item like 'phone%' 
                      group by customer_ID 
                      having count(distinct case when item like 'phone%' then 1 else item end) >= 2)

Upvotes: 3

DanB
DanB

Reputation: 2124

select customer_ID from myTable where item = 'box' or item like 'phone%'

Upvotes: 1

Related Questions