0

I have a table having details such that

Userpin  product       Time1      contains
4556     23            7:30         A
4557     23            7:30         B
4558     23            7:30         C
4559     23            7:30         D
4560     23            7:30         E
4561     24            7:30         A
4562     24            7:30         B
4563     24            7:30         C
4564     24            7:30         D
4565     24            7:30         E
4566     25            7:30         A
4567     25            7:30         A
4568     23            7:30         F
4568     23            7:30         G

So with the above table, I need all products that contains (A,B,C,D,E,F) all of them.

With the given table, I am expecting 23 as the result (Although product 23 contains G as well, but that does not bother me.).

1
  • 1
    Including a minimal reproducible example is generally considered good etiquette. I suggest dbfiddle.uk Including your DDL, sample data, and a query or two that you've tried unsuccessfully so far will help others answer (and may provide you with the answer directly just setting it up!) Feb 20, 2020 at 11:16

1 Answer 1

3

I have recreated your table in tempdb using the Adventure Works Database. This code basically selects all the records that have column contains ('A', 'B', 'C', 'D', 'E', 'F') and then does a distinct count on the [contains] column. When the result is six the record must have a row of all of ('A', 'B', 'C', 'D', 'E', 'F').


Create Table #Table (Userpin int, product int, [time] time, [contains] varchar(1))
Insert into #Table (Userpin, product, [time], [contains])
Select 4556,     23,            '7:30',         'A' UNION ALL
Select 4557,     23,            '7:30',         'B' UNION ALL
Select 4558,     23,            '7:30',         'C' UNION ALL
Select 4559,     23,            '7:30',         'D' UNION ALL
Select 4560,     23,            '7:30',         'E' UNION ALL
Select 4561,     24,            '7:30',         'A' UNION ALL
Select 4562,     24,            '7:30',         'B' UNION ALL
Select 4563,     24,            '7:30',         'C' UNION ALL
Select 4564,     24,            '7:30',         'D' UNION ALL
Select 4565,     24,            '7:30',         'E' UNION ALL
Select 4566,     25,            '7:30',         'A' UNION ALL
Select 4567,     25,            '7:30',         'A' UNION ALL
Select 4568,     23,            '7:30',         'F' UNION ALL
Select 4568,     23,            '7:30',         'G' 

Select product, count(distinct [contains]) as count_distinct_contains
from #Table
where [contains] In ('A', 'B', 'C', 'D', 'E', 'F')
GROUP BY product 
HAVING count(distinct [contains]) = 6

This is the Microsoft SQL Server syntax.

3
  • If op:s version of MySQL supports CTE:s, a generalization can be: with search (s) as ( select 'ABCDEF' ) select t.product, search.s from T as t join search on locate(t.contains, search.s) > 0 group by t.product, search.s having count(distinct t.contains) = length(search.s) Feb 20, 2020 at 11:53
  • Thanks .. It works for me.
    – Gaurav Raj
    Feb 21, 2020 at 3:36
  • No problem. Glad it works :) Feb 21, 2020 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.