I have the following tables.
Table JUGADOS
| Field | Type | Null | Key | Default | Extra
+------------+------------------+------+-----+---------+----------------
| FECHA | date | YES | | NULL |
| SERIE | bigint(12) | YES | | 0 |
| J1 | tinyint(2) | YES | | 0 |
| J2 | tinyint(2) | YES | | 0 |
| J3 | tinyint(2) | YES | | 0 |
| J4 | tinyint(2) | YES | | 0 |
| J5 | tinyint(2) | YES | | 0 |
| J6 | tinyint(2) | YES | | 0 |
| JUGADOSID | int(10) unsigned | NO | PRI | NULL | auto_increment
Table SALIDOS
Field | Type | Null | Key | Default | Extra
------------+------------------+------+-----+---------+----------------
FECHA | date | YES | | NULL |
SERIE | bigint(14) | YES | | 0 |
S1 | tinyint(2) | YES | | 0 |
S2 | tinyint(2) | YES | | 0 |
S3 | tinyint(2) | YES | | 0 |
S4 | tinyint(2) | YES | | 0 |
S5 | tinyint(2) | YES | | 0 |
S6 | tinyint(2) | YES | | 0 |
S7 | tinyint(2) | YES | | 0 |
SALIDOSID | int(10) unsigned | NO | PRI | NULL | auto_increment
The JUGADOS table keeps plays by date, the plays are 6 numbers ranging from 0 to 45 , and do not repeat. Those 6 numbers are saved each in the fields J1 to J6 . The series field what you save is the formation of those 6 numbers, for example, if you played 3,10,21,25,31,37 , the series will be 31021253137 . The relationship with the SALIDOS table is the fecha field.
In the table SALIDOS the raffle was made on the date of the table JUGADOS , and for each date there are 3 exits, an example would be:
Table: JUGADOS
Fecha Serie S1 S2 S3 S4 S5 S6
03/04/2016 4,14,15,22,26,39 4 14 15 22 24 39
Table: SALIDOS
Fecha Serie S1 S2 S3 S4 S5 S6
03/04/2016 4,11,22,26,32,38 4 11 22 26 32 38
03/04/2016 4,12,23,27,33,39 4 12 23 27 33 39
03/04/2016 5,13,24,27,34,39 5 13 24 27 34 39
In this example, the number of hits played by the numbers played was 4 , because the numbers in the SALIDOS table can be repeated, but only those that do not repeat are taken into account, in the example repeat the 4 , the 27 and the 39 .
What I need to create is a SELECT that returns me the hits that each of the plays of the JUGADOS table had within a range of dates in the SALIDOS table. For example, to know how many successes each move in table JUGADOS had between the 01/01/2016 and the 31/12/2016 of the table SALIDOS .
Thanks ... but it did not work, that way it returns all the plays, it's as if I had done:
SELECT * FROM JUGADOS
I have replaced the LEFT JOIN with INNER JOIN and it returns " Empty Set " (testing from the command line), it happens the same if I do it with RIGHT JOIN .
Specifically what I need is eg:
The numbers 1,10,21,22,28,36 that were played on 03/27/2010 had 5 hits within the 18 numbers that came out on 02/04/2016, the numbers 6,13, 14,30,34,39 that were played on 1/16/2008 had 4 hits within the 18 issues that came out on 07/28/2016, etc., etc. Graphically it would be something like:
Números jugados Fecha > Cant Aciertos Fecha > acierto
1,10,21,22,28,36 27/03/2010 5 02/04/2016
6,13,14,30,34,39 16/01/2008 4 28/07/2016
Then I can sort it by the column with the most hits and by the date of maximum success, but in principle the most difficult thing is to find the way to get it out this way.
What you have to keep in mind is that there are 18 numbers that come out for the same date of the play, and 6 that are played, and that within the 18 numbers are only taken once if they are repeated, so if the day 04/02/2016 The numbers 1,11,21,23,29,37 | 1,13,24,29,36 | 2,10,20,22,40,42 came out
in the example we repeat the 1 and the 29, and to evaluate the successes we would take the numbers 1,11,21,23,29,37,13,24,36,2,10,20,22,40,42, or instead of 18 numbers we will take 16.
06/01/2017
SELECT (
IF(
COALESCE(j1.j1,0)>0,1,0)
+IF(COALESCE(j2.j2,0)>0,1,0)
+IF(COALESCE(j3.j3,0)>0,1,0)
+IF(COALESCE(j4.j4,0)>0,1,0)
+IF(COALESCE(j5.j5,0)>0,1,0)
+IF(COALESCE(j6.j6,0)>0,1,0)
) AS aciertos
FROM jugados AS j
LEFT JOIN (
SELECT DISTINCT(s1) AS j1
FROM salidos
WHERE fecha BETWEEN '2016-01-01' AND '2016-12-31'
) AS j1 ON j.j1=j1.j1
LEFT JOIN (
SELECT DISTINCT(s2) AS j2
FROM salidos
WHERE fecha BETWEEN '2016-01-01' AND '2016-12-31'
) AS j2 ON j.j2=j2.j2
LEFT JOIN (
SELECT DISTINCT(s3) AS j3
FROM salidos
WHERE fecha BETWEEN '2016-01-01' AND '2016-12-31'
) AS j3 ON j.j3=j3.j3
LEFT JOIN (
SELECT DISTINCT(s4) AS j4
FROM salidos
WHERE fecha BETWEEN '2016-01-01' AND '2016-12-31'
) AS j4 ON j.j4=j4.j4
LEFT JOIN (
SELECT DISTINCT(s5) AS j5
FROM salidos
WHERE fecha BETWEEN '2016-01-01' AND '2016-12-31'
) AS j5 ON j.j5=j5.j5
LEFT JOIN (
SELECT DISTINCT(s6) AS j6
FROM salidos
WHERE fecha BETWEEN '2016-01-01' AND '2016-12-31'
) AS j6 ON j.j6=j6.j6
WHERE j.fecha BETWEEN '2016-01-01' AND '2016-12-31'
) ;