search multiple rows with the same values sql

Question

I have a table in my database which has the following structure:

ID | Date | Ball1 | Ball2 | Ball3 | Ball4 | Ball5 | Ball6 | Ball7 | BonusBall

All the Ball rows are just integers.

What I want to do now is to write a select query that selects all the rows that have multiple of the same values in it.

For instance all the rows that contain 1 and 12.

I tried the following query, but that doesn't quite give my what I want:

SELECT * FROM number 
WHERE ball1 OR ball2 OR ball3 OR ball4 OR ball5 OR ball6 OR bonusball IN (1,12);

Am I on the right track? Any suggestions?

Sorry everyone I should actually explain this a little better I don't want any rows that contain either 1 or 12 but just the ones that contain both. Thanks in advance! — Liam Barnett
– Liam Barnett, Commented Aug 3, 2015 at 22:12

ThomasVdBerge · Accepted Answer · 2015-08-04 07:20:45Z

1

Your solution was already pretty good, but if you want to check it for each field, I believe you have to write it like this:

SELECT * FROM test
WHERE (ball1 = 1 OR ball2 = 1 OR ball3  = 1  
OR ball4  = 1 OR ball5 = 1 OR ball6  = 1 OR bonusball = 1)
AND (ball1 = 12 OR ball2 = 12 OR ball3 = 12  
OR ball4 = 12 OR ball5 = 12 OR ball6 = 12 OR bonusball = 12);

Although I would not advise running this query in a high traffic environment. A better idea would be to have two tables. One containing 'Lottery' and the other one 'Balls'. Where Balls would have a reference to the lottery table.

You should try to read up on Database normalization.

edited Aug 4, 2015 at 7:20

answered Aug 3, 2015 at 20:32

ThomasVdBerge

8,2085 gold badges48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Liam Barnett Over a year ago

Thanks very much! I just needed to get verification that I was doing it correctly. Many thanks Liam

Liam Barnett Over a year ago

Actually I should probably explain a little better this does bring up rows that contain 1 and12 for instance however I was it to bring up rows that contain both 1 and 12 only? Thanks in advance!

ThomasVdBerge Over a year ago

I've changed my answer to reflect this. This should work. Mark my answer as the solution if it helped you :-)

Randy · Accepted Answer · 2015-08-03 20:32:50Z

0

first of all - you should practice 'normalization'

second a better syntax would be

SELECT * 
FROM number 
WHERE ball1 in (1,12) 
OR ball2 in (1,12) 
OR ball3 in (1,12)

etc

answered Aug 3, 2015 at 20:32

Randy

16.7k2 gold badges40 silver badges56 bronze badges

1 Comment

Liam Barnett Over a year ago

Thanks for the advice it is always better practice to use the correct syntax so thanks for this too Randy! Also you were so fast to respond! Thanks!

Collectives™ on Stack Overflow

search multiple rows with the same values sql

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related