SELECT subgroup of values

Question

Given two tables 'Draws' (id, date) AND 'Results' (id, draw_id, number) where each Draw has x results, how can I get the draws that have a subgroup of numbers within their results?

Draws
-----------------
Id | Date
-----------------
1  | 2015-01-20
2  | 2015-01-22
-----------------

Results
--------------------
Id  | Draw | Number
--------------------
1   | 1    | 13
2   | 1    | 15
3   | 1    | 22
4   | 1    | 36
5   | 1    | 45
6   | 2    | 11
7   | 2    | 15
8   | 2    | 22

How can I get the draw that has (15,22,45) within its results? (In the example draw #1)

Do you mean return the draw(s) that have 15, 22 and 45 in the results? IE only draws that have all 3? — Fred, Jan 13 '16 at 20:29
@Fred yes only draws that have all 3 even if they have more. — Chris Athanasiadis, Jan 13 '16 at 20:33

Mike Purcell · Accepted Answer · 2016-01-14T01:28:30.583

1

Good catch by Fred, this will return the correct results:

SELECT
    d.*
FROM
    Draw AS d
INNER JOIN Results AS r1 ON r1.Draw = d.ID AND r1.Number = 15
INNER JOIN Results AS r2 ON r2.Draw = d.ID AND r2.Number = 22
INNER JOIN Results AS r3 ON r3.Draw = d.ID AND r3.Number = 45
GROUP BY
    d.id;

Fred's answer will work, but tbh it's fragile in that it will only work if you are looking for 3 results. My answer is more deliberate, however you will have to construct the query programatically to determine how many joins are necessary to get the results you want, for example:

15 -> 1 join
15, 22 -> 2 joins
15, 22, 45 -> 3 joins
....

edited Jan 14 '16 at 01:28

answered Jan 13 '16 at 20:26

Mike Purcell

19,847
10
52
89

1

This returns both Draws not just draws that have all 3 numbers in the result. It also has a rogue comma after r.* :) – Fred Jan 13 '16 at 20:55
1

Rogue has been slain – Mike Purcell Jan 13 '16 at 21:00
Yes @Fred you are right. Mike Purcell thanks for the update. Indeed the query is constructed programmatically in order to work in all cases. Thank you guys! – Chris Athanasiadis Jan 14 '16 at 11:45

score 0 · Answer 2 · answered Jan 13 '16 at 20:23

0

Per your question you don't need a join or anything...

select Draw from 
Results r
where r.Number in (15,22,45);

answered Jan 13 '16 at 20:23

FirebladeDan

1,069
6
14

Yeap that was the case since the beginning. Thanks for your answer. – Chris Athanasiadis Jan 13 '16 at 20:39

score 0 · Answer 3 · answered Jan 13 '16 at 20:30

0

This is another possibility (without duplicates)

select * from Draws d where exists (select * from Results r where r.Draw=d.Id and Number in (1, 2, 3...) );

answered Jan 13 '16 at 20:30

marian0

664
6
15

That's a horrible query for something like this... you can just do DISTINCT – FirebladeDan Jan 13 '16 at 20:34
Take a look at [this](http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance). – marian0 Jan 13 '16 at 20:36
Thanks for the answer. :) – Chris Athanasiadis Jan 13 '16 at 20:40

score 0 · Answer 4 · answered Jan 13 '16 at 20:38

0

You just need to use JOIN for getting this result

SELECT * FROM Draw, Results 
WHERE Draw.ID = Results.DrawID 
AND Results.Number IN (15,22,45);

In Result:

You will get the Results ids (2, 3, 5, 7, 8)

answered Jan 13 '16 at 20:38

devpro

16,184
3
27
38

Fred · Answer 5 · 2016-01-13T21:24:15.853

0

SELECT Draw FROM Results
Where Number IN (15,22,45)
Group By Draw
Having Count(*) > 2

Will give you only Draws that have all 3

SELECT d.Date, r.Draw FROM #result r Join #Draws d ON d.id = r.Draw
Where Number IN (15,22,45)
Group By d.Date, r.Draw
Having Count(*) > 2

If you want the date in the results.

edited Jan 13 '16 at 21:24

answered Jan 13 '16 at 21:15

Fred

5,663
4
45
74

SELECT subgroup of values

5 Answers5