Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you're willing to normalize the data by splitting the list of numbers into a sub-table, then you could trivially determine winners with something like:</p> <pre><code>SELECT LotteryTicket.TicketID, GROUP_CONCAT(LotteryTicketNumbers.number), COUNT(LotteryTicketNumbers.number) AS cnt FROM LotteryTicket LEFT JOIN LotterYTicketNumbers ON (LotteryTicketNumbers.number IN (winning, numbers, here)) GROUP BY LotteryTicket.TicketID HAVING cnt &gt;= 3; </code></pre> <p>where the '3' represents the mininum number of matched numbers required to win any prize. This won't handle "bonus" numbers, if there's any, though you could repeat the same query and flag any draws where the bonus number is present with a derived field.</p> <p>Note that this isn't tested, just going off the top of my head, so probably has some syntax errors.</p> <hr> <p>comment followup:</p> <p>GROUP_CONCAT is a mysql-specific sql extension. You can rip that out since it would seem you're on SQLserver.</p> <p>The 'LottoTicketNumbers' is what you'd use to normalize your tables. Instead of a single monolitic "ticket" record, you split it into two tables:</p> <pre><code>LottoTicket: ticketID, drawDate LottoTicketNumbers: ticketID, drawNumber </code></pre> <p>So let's say you had a ticket for the Apr 1/2011 draw, with numbers 1,12,23,44,55, you'd end up with something like:</p> <pre><code>LottoTicket: ticketID = 1, drawDate = Apr 1/2011 LottoTicketNumbers: (1,1), (1,12), (1,23), (1,44), (1,55) </code></pre> <p>Structuring your tables like this makes the query work, using some basic set theory and the power of a relational database. The original table structure makes it nearly impossible to do the comparisons necessary to figure out all the possible permutations of winning numbers, you'd end up some hideous construct like</p> <pre><code>select ... where (number1 in (winning, numbers here), number2 in (winning, numbers, here), number3 in (winning, numbers,here), etc.... </code></pre> <p>and wouldn't tell you exactly which prize you'd won (matched 3, matched 5 + bonus, etc...).</p> <p>Example query results:</p> <p>Let's say the draw numbers are 10,20,30,40,50, and you've got a ticket with 10,20,30,42,53. You've matched 3 of the 5 draw numbers, and win $10. Using the normalized table structure above, you'd have tables like:</p> <pre><code>LottoTicket: id #203, drawDate: Apr 1/2011 LottoTicketNumbers: (203, 10), (203, 20), (203, 30), (203, 42), (203, 53) </code></pre> <p>And the query would be</p> <pre><code>SELECT LottoTicket.TicketID, COUNT(LottoTicketNumbers.number) AS cnt FROM LottoTicket LEFT JOIN LottoTicketNumbers ON (LottoTicketNumbers.number IN (10,20,30,40,50)) GROUP BY LottoTicket.TicketID HAVING CNT &gt;= 3 </code></pre> <p>You'd get (ungrouped) results of</p> <pre><code>203, 10 203, 20 203, 30 </code></pre> <p>and with the grouping/aggregate functions:</p> <pre><code>203, 3 // ticket #203 matched 3 numbers. </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCheck for winning tickets in lottery using SQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMarc B
    1. USMarc B
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCheck for winning tickets in lottery using SQL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI've having huge problems getting this to run (or even trying to figure out what you are trying to achieve). First GROUP_CONCAT is not a recognized keyword (SQL 2008 R2). Second where does LotteryTicketNumbers come from?
      singulars
      1. PO
      1. USPaaland
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload