Lyrical.me Lyrical.me - 2 months ago 7
SQL Question

Compare hundreds of rows of data against one in another table

I have an interesting problem that is actually causing me so much grief.

I have 2 tables that are identical in makeup. One table holds winning lottery numbers for a given draw, the other holds 500 randomly generated "tickets" for that draw.

I am trying to write some code that will tell me how many of the 500 tickets have 3 matching numbers, 4 matching numbers, 5 matching numbers and of course all 6 matching numbers.

the table structure for both tables is

id, playDate, num1, num2, num3, num4, num5, num6.

Here is where I'm at and I know this isn't correct before anyone says anything. Really struggling here:

SELECT count(*) AS c, w.*, l.*
FROM winningNums AS w, lottoNums AS l
WHERE w.playDate = "2016-10-10"
AND l.playDate = "2016-10-10"
AND l.num1 (
w.num1, w.num2, w.num3, w.num4, w.num5, w.num6
)


I've also been batting something like this around

SELECT *
FROM winningNums AS w
INNER JOIN lottoNums AS l
ON w.playDate = l.playDate
AND w.num1 = l.num1
AND w.num2 = l.num2
AND w.num3 = l.num3
AND w.num4 = l.num4
AND w.num5 = l.num5
AND w.num6 = l.num6


The main problem i'm facing is that a 3-match win could come from num1, num3, and num4 (from the ticket) matching up with num2, num5 and num6 (from the winning numbers).

Help :-)

Answer
SELECT l.*,
    l.num1 IN (w.num1, w.num2, w.num3, w.num4, w.num5, w.num6) +
    l.num2 IN (w.num1, w.num2, w.num3, w.num4, w.num5, w.num6) +
    l.num3 IN (w.num1, w.num2, w.num3, w.num4, w.num5, w.num6) +
    l.num4 IN (w.num1, w.num2, w.num3, w.num4, w.num5, w.num6) +
    l.num5 IN (w.num1, w.num2, w.num3, w.num4, w.num5, w.num6) +
    l.num6 IN (w.num1, w.num2, w.num3, w.num4, w.num5, w.num6)
    AS hits
FROM winningNums AS w
CROSS JOIN lottoNums AS l
WHERE w.playDate = "2016-10-10"
  AND l.playDate = "2016-10-10"
HAVING hits >= 3
ORDER BY hits DESC

This will compare every number from ticket with every number from lottery. And all hits/matches will be added.