CODEWITHSUNDEEP
sqlsql-server-2008
db-rocker
db-rocker

Reputation: 51

SQL - Return results of row data

So I have the following table (see below) and I'd like to write a query to return me all the game numbers that have the following in all three ticket columns.

E.G. give me ALL game numbers that have 1,2 and 3 in them.

The numbers don't specifically have to be in certain columns (like 1 in Ticket1 etc...). They just have to all be in the Game's row.

I'd post what I have of the query so far but it's completely useless and really need a fresh pair of eyes to see if this is possible and if so, how?

enter image description here

Upvotes: 0

Views: 67

Answers (5)

Andy Refuerzo
Andy Refuerzo

Reputation: 3332

E.G. give me ALL game numbers that have 1,2 and 3 in them.

The numbers don't specifically have to be in certain columns (like 1 in Ticket1 etc...). They just have to all be in the Game's row.

select Game 
from [table-name] 
where 
(Ticket1 ='1' and Ticket2 ='2' and Ticket3 ='3') or 
(Ticket1 ='1' and Ticket2 ='3' and Ticket3 ='2') or 
(Ticket1 ='2' and Ticket2 ='1' and Ticket3 ='3') or 
(Ticket1 ='2' and Ticket2 ='3' and Ticket3 ='1') or 
(Ticket1 ='3' and Ticket2 ='1' and Ticket3 ='2') or 
(Ticket1 ='3' and Ticket2 ='2' and Ticket3 ='1')

This returns 443.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

The answer by @Andy looks correct, but hopefully you can see what a hassle it will be to work with your table in its current form. Instead, I propose that you store each game-ticket relation in a separate row. Here is what the first six records would look like with your current data:

Game | Ticket
443  | 1
443  | 2
443  | 3
444  | 57
444  | 13
444  | 33

Now it is easy to identify all games having a set of three certain tickets, e.g.

SELECT Game
FROM yourTable
WHERE Ticket IN (1, 2, 3)
GROUP BY Game
HAVING COUNT(DISTINCT Ticket) = 3

Upvotes: 0

Garima
Garima

Reputation: 1

I didn't understand the exact requirement so here are 2 interpretations:

To get all Game numbers that have the exact numbers 1,2,3 in the same row:

select Game from [table-name] where Ticket1 in (1,2,3) and Ticket2 in (1,2,3) and Ticket3 in (1,2,3)

To get all Game numbers that have values containing 1,2,3 in the same row:

select Game from [table-name] where (Ticket1 like '%1%' or Ticket1 like '%2%' or Ticket1 like '%3%') and (Ticket2 like '%1%' or Ticket2 like '%2%' or Ticket2 like '%3%') and (Ticket3 like '%1%' or Ticket3 like '%2%' or Ticket3 like '%3%')

Upvotes: 0

Serg
Serg

Reputation: 22811

Cross apply a conditional count to the union of tickets. 

select * 
from myTable
cross apply ( 
   select count(case when ticket in (1,2,3) then ticket end) cnt
   from (
      select Ticket1  ticket 
      union
      select Ticket2  ticket 
      union
      select Ticket3  ticket
   ) t
) t
where t.cnt = 3

Upvotes: 0

kots
kots

Reputation: 465

You can do the following

select * from Table1
where (
cast(Ticket1 as varchar(100))
+ ',' + 
cast(Ticket2 as varchar(100))
+ ',' + 
cast(Ticket3 as varchar(100))) in 
('1,2,3','1,3,2','2,1,3','2,3,1','3,1,2','3,2,1')

Upvotes: 0

Related Questions