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
    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!) – Peter Vandivier Feb 20 at 11:16
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.

|improve this answer|||||
  • 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) – Lennart Feb 20 at 11:53
  • Thanks .. It works for me. – Gaurav Raj Feb 21 at 3:36
  • No problem. Glad it works :) – Alchemical29 Feb 21 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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