Filter the record with matching names

Transact-SQL
1

Hi,
Below is an order table
declare @orderInfo table(orderNumber int,itemName varchar(50),itemid int)

insert into @orderInfo values
(101,'Sink x',1)
,(101,'Tap x',2)
,(102,'Sink Y',3)
,(102,'Tap Y',4)
,(103,'Hinges',5)
,(103,'Table',6)

select * from @orderInfo

Would like to get the order having both sink and tap (ie,101,102).
when check itemname like '%sink%' and itemname like '%Tap%' ,it does not return any record

2

There are some ways to do this , here is one example:

select Distinct OrderNumber 
from @orderInfo as O
where
    exists(select 1 
                from @OrderInfo as o1 
                where o1.OrderNumber = o.OrderNumber
                      and o1.itemName like '%sink%')
    and
    exists(select 1 
                from @OrderInfo as o2 
                where o2.OrderNumber = o.OrderNumber
                      and o2.itemName like '%Tap%')

Output:

OrderNumber
101
102

dbfiddle

1 Like
3

hi

i think it should be

-- yours
when check itemname like '%sink%' and itemname like '%Tap%'
--- my solution .
where itemname like '%Sink%' OR itemname like '%Tap%'

please note the following things

  1. when changed to where
  2. sink changed to Sink .. case sensitive
  3. and changed to OR
  4. check word removed in mine ..

hope it helps
:slight_smile:
:slight_smile:

create data 
declare @orderInfo table(orderNumber int,itemName varchar(50),itemid int)

insert into @orderInfo values
(101,'Sink x',1)
,(101,'Tap x',2)
,(102,'Sink Y',3)
,(102,'Tap Y',4)
,(103,'Hinges',5)
,(103,'Table',6)
SQL 
SELECT * 
FROM   @orderInfo 
WHERE  itemname LIKE '%Sink%' 
        OR itemname LIKE '%Tap%'
Result

image

4 
select orderNumber
from @orderInfo
group by orderNumber
having max(case when itemName like '%Sink%' then 1 else 0 end) = 1 and
    max(case when itemName like '%Tap%' then 1 else 0 end) = 1
order by orderNumber

As another example, let's say you wanted orders that had 'Sink' but did not have 'Tap':
insert into @orderInfo values
(104,'Sink Y',7)

select orderNumber
from @orderInfo
group by orderNumber
having max(case when itemName like '%Sink%' then 1 else 0 end) = 1 and
    max(case when itemName like '%Tap%' then 1 else 0 end) = 0 --<<--chg to 0=NOT have
order by orderNumber
1 Like
5

Thanks for the different solution.
Consider the case like this with one more order added.

   declare @orderInfo table(orderNumber int,itemName varchar(50),itemid int)

    insert into @orderInfo values
    (101,'Sink x',1)
    ,(101,'Tap x',2)
    ,(102,'Sink Y',3)
    ,(102,'Tap Y',4)
    ,(103,'Hinges',5)
    ,(103,'Table',6)
    ,(104,'Sink with Tap',7)
    ,(104,'Hinges',8)

    select * from @orderInfo

    In this case, don't want to return 104 record, only 101and 102.

The idea was if the record contain sink and tap in different records of same order, fetch that
result, ie, bought the products separately, not as a single product.

6

Add

itemName like '%sink%' and itemName not like '%tap%'

and

itemName like '%Tap%' and itemName not like '%sink%'
7

select ordernumber,item
from
(
select
ordernumber ,
case
when itemname like '%x%' then 'x'
when itemname like '%y%' then 'y'
end item
from
@orderInfo
)x
where item is not null
group by ordernumber,item

8

The idea was if the record contain sink and tap in different records of same order, fetch that
result, ie, bought the products separately, not as a single product.

select orderNumber
from @orderInfo
where itemName like '%Sink%' or itemName like '%Tap%' --<<-- add this
group by orderNumber
having max(case when itemName like '%Sink%' then 1 else 0 end) = 1 and
    max(case when itemName like '%Tap%' then 1 else 0 end) = 1 and
    count(*) >= 2 --<<-- add this
order by orderNumber