MariaDB [mart]> EXPLAIN efis_film_festival; +------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | language | varchar(5) | NO | | NULL | | | film_id | int(10) unsigned | NO | MUL | NULL | | | festival_id | int(10) unsigned | YES | MUL | NULL | | | festival_year | varchar(50) | NO | | NULL | | | participation_code | varchar(100) | NO | | NULL | | | participation_relation | text | NO | | NULL | | | seq | int(10) unsigned | NO | | NULL | | | created | datetime | NO | | NULL | | | created_by | int(10) unsigned | NO | | NULL | | | updated | datetime | NO | | NULL | | | updated_by | int(10) unsigned | NO | | NULL | | +------------------------+------------------+------+-----+---------+----------------+ MariaDB [mart]> SELECT * FROM efis_film_festival limit 3; +-----+----------+---------+-------------+---------------+-----------------------------------+-------------------------------------------------------------------+-----+---------------------+------------+---------------------+------------+ | id | language | film_id | festival_id | festival_year | participation_code | participation_relation | seq | created | created_by | updated | updated_by | +-----+----------+---------+-------------+---------------+-----------------------------------+-------------------------------------------------------------------+-----+---------------------+------------+---------------------+------------+ | 260 | et | 935 | 1203 | 2010 | FILM_FESTIVAL_PARTICIPATION_AWARD | Parim eksperimentaalfilm, parim operaatoritöö. | 1 | 2011-10-24 23:34:22 | 29 | 2011-10-24 23:34:22 | 29 | | 430 | et | 443 | 1359 | 1995 | FILM_FESTIVAL_PARTICIPATION_AWARD | Esimene auhind "Merineitsi" debüütide katefoorias | 1 | 2011-11-06 11:51:19 | 22 | 2011-11-06 11:51:19 | 22 | | 431 | et | 443 | 1392 | 1994 | FILM_FESTIVAL_PARTICIPATION_AWARD | Pälvis operaatoritöö eest linnavõimude diplomi ja käekella. | 2 | 2011-11-06 11:51:19 | 22 | 2011-11-06 11:51:19 | 22 | +-----+----------+---------+-------------+---------------+-----------------------------------+-------------------------------------------------------------------+-----+---------------------+------------+---------------------+------------+ MariaDB [mart]> SELECT * FROM efis_film_festival ORDER BY film_id limit 30; +--------+----------+---------+-------------+---------------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----+---------------------+------------+---------------------+------------+ | id | language | film_id | festival_id | festival_year | participation_code | participation_relation | seq | created | created_by | updated | updated_by | +--------+----------+---------+-------------+---------------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----+---------------------+------------+---------------------+------------+ | 169990 | en | 7 | 1092 | 2005 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 1 | 2016-04-22 15:06:58 | 150 | 2016-04-22 15:06:58 | 150 | | 169991 | en | 7 | 1111 | 2005 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 2 | 2016-04-22 15:06:58 | 150 | 2016-04-22 15:06:58 | 150 | | 169992 | et | 7 | 1092 | 2005 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 1 | 2016-04-22 15:07:00 | 150 | 2016-04-22 15:07:00 | 150 | | 169993 | et | 7 | 1111 | 2005 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 2 | 2016-04-22 15:07:00 | 150 | 2016-04-22 15:07:00 | 150 | | 178058 | et | 8 | 1344 | 1983 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | Diplom poliitilise detektiivžanri eduka viljelemise eest režissöör Peeter Urblale | 1 | 2016-08-11 09:38:43 | 150 | 2016-08-11 09:38:43 | 150 | | 178059 | en | 8 | 1344 | 1983 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | Diploma for the successful development of the political detective film genre given to the director Peeter Urbla | 1 | 2016-08-11 09:39:03 | 150 | 2016-08-11 09:39:03 | 150 | | 178056 | et | 9 | 1344 | 1983 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | Diplom poliitilise detektiivžanri eduka viljelemise eest režissöör Peeter Urblale | 1 | 2016-08-11 09:37:50 | 150 | 2016-08-11 09:37:50 | 150 | | 178057 | en | 9 | 1344 | 1983 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | Diploma for successful development of the political detective genre given to director Peeter Urbla | 1 | 2016-08-11 09:38:08 | 150 | 2016-08-11 09:38:08 | 150 | | 172328 | en | 13 | 2509 | 2001 | FILM_FESTIVAL_PARTICIPATION_AWARD | Grand Prix and the best Director | 1 | 2016-05-03 19:29:41 | 150 | 2016-05-03 19:29:41 | 150 | | 172329 | en | 13 | 1196 | 2001 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 2 | 2016-05-03 19:29:41 | 150 | 2016-05-03 19:29:41 | 150 | SELECT film_id, festival_id, COUNT(*) FROM efis_film_festival GROUP BY film_id, festival_id ORDER BY COUNT(*) DESC LIMIT 20; -- grupeerige ka aasta järgi ja näidake aastat +---------+-------------+----------+ | film_id | festival_id | COUNT(*) | +---------+-------------+----------+ | 249 | 1607 | 6 | | 16147 | 1884 | 6 | | 249 | 1086 | 6 | | 771 | 1729 | 6 | | 5817 | 1943 | 6 | | 16054 | 1197 | 6 | | 18112 | 2679 | 6 | | 2647 | 1908 | 6 | | 6296 | 1908 | 6 | | 17567 | 2679 | 5 | | 18178 | 2679 | 5 | | 18177 | 2679 | 5 | | 10774 | 1080 | 5 | | 16147 | 2207 | 4 | | 771 | 1534 | 4 | | 1732 | 1996 | 4 | | 17104 | 2010 | 4 | | 2497 | 1186 | 4 | | 17105 | 1186 | 4 | | 17105 | 1884 | 4 | +---------+-------------+----------+ SELECT film_id, festival_id, festival_year, COUNT(*) FROM efis_film_festival GROUP BY film_id, festival_id, festival_year ORDER BY COUNT(*) DESC LIMIT 20; +---------+-------------+---------------+----------+ | film_id | festival_id | festival_year | COUNT(*) | +---------+-------------+---------------+----------+ | 249 | 1086 | 2009 | 6 | | 249 | 1607 | 2009 | 6 | | 838 | 2375 | 2011 | 4 | | 100 | 1475 | 2002 | 4 | | 249 | 1081 | 2008 | 4 | | 17485 | 3257 | 2017 | 4 | | 4368 | 1497 | 2003 | 4 | | 508 | 1846 | 2010 | 4 | | 3476 | 1206 | 1984 | 4 | | 17455 | 3257 | 2017 | 4 | SELECT * FROM efis_film_festival WHERE film_id=249 AND festival_id=1086; +--------+----------+---------+-------------+---------------+-------------------------------------------+------------------------+-----+---------------------+------------+---------------------+------------+ | id | language | film_id | festival_id | festival_year | participation_code | participation_relation | seq | created | created_by | updated | updated_by | +--------+----------+---------+-------------+---------------+-------------------------------------------+------------------------+-----+---------------------+------------+---------------------+------------+ | 163149 | et | 249 | 1086 | 2009 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 19 | 2015-12-06 15:30:52 | 150 | 2015-12-06 15:30:52 | 150 | | 163152 | et | 249 | 1086 | 2009 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 22 | 2015-12-06 15:30:52 | 150 | 2015-12-06 15:30:52 | 150 | | 163168 | et | 249 | 1086 | 2009 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 38 | 2015-12-06 15:30:52 | 150 | 2015-12-06 15:30:52 | 150 | | 163189 | en | 249 | 1086 | 2009 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 19 | 2015-12-06 15:31:29 | 150 | 2015-12-06 15:31:29 | 150 | | 163192 | en | 249 | 1086 | 2009 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 22 | 2015-12-06 15:31:29 | 150 | 2015-12-06 15:31:29 | 150 | | 163208 | en | 249 | 1086 | 2009 | FILM_FESTIVAL_PARTICIPATION_PARTICIPATION | | 38 | 2015-12-06 15:31:29 | 150 | 2015-12-06 15:31:29 | 150 | +--------+----------+---------+-------------+---------------+-------------------------------------------+------------------------+-----+---------------------+------------+---------------------+------------+ 6 rows in set (0.02 sec) MariaDB [mart]> SELECT COUNT(*) FROM efis_festival; +----------+ | COUNT(*) | +----------+ | 1740 | +----------+ SELECT GROUP_CONCAT(seq), festival_id FROM efis_film_festival GROUP BY festival_id LIMIT 10; MariaDB [mart]> SELECT GROUP_CONCAT(seq), festival_id FROM efis_film_festival GROUP BY festival_id LIMIT 10; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | GROUP_CONCAT(seq) | festival_id | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | 1,1,3,3,1,1,7,7,10,10,1,1 | 1076 | | 16,6,37,37,11,38,31,31,5,5,22,22,16,11 | 1077 | | 21,21,2,9,17,33,2,2,17,28,2,7,7,22,21,39,19,19,16,16,24,24,40,39,23,23,36,36,36,36,36,36,36,36,36,36,36,36,36,36,37,37,9,31,31,13,13,3,3,37,32,32,9,9,18,26,26,8,8,14,14,10,10,18,10,10,14,14,6,16,16,34,34,19,10,10,3,3,33,6,39,38,20,10,10,18,18,11,6,6,11,13,13,28,25,25,23,23,26 | 1078 | | 3,5,5,1,5,21,14,13,30,30,6,6,20,20,14,14,23,23,23,23,23,23,23,23,23,23,23,23,23,23,24,24,5,15,15,27,27,38,17,48,5,5,21,21,4,17,10,38,10,38,5,5,1,17,24,24,34,34,1,27,27,36,36,10,21,1,17,18,18,3,38,10,22,13,13,86,19,19,13,13,48,3,3,5,5,8,1,2,2,21,4,4,86,7,1 | 1079 | | 4,4,1,1,10,4,4,1,5,10,38,10,38,1,10,15,15,4,81,10,10,4,15,14,4,83,28,37,28,37,14,22,14,22,28,28,27,63,62,4,13,4,13,5,14,5,14,4,14,4,14,5,14,5,14,5,14,5,14,5,14,5,14,5,14,5,14,6,15,6,15,10,64,64,5,9,1,5,5,3,40,40,1,9,9,27,27,12,12,24,24,3,4,2,8,8,29,33,29,33,26,102,26,102,1,6,3,3,9,9,4,16,16,6,5,29,27,4,83,5,1,6,6,4,165,7,7,9,13,13,7,63,7,63,15,15,6,6,1,6,6,10,2,3,4,9,13,3,8,8,8,5,12,12,3,81,2,13,4,4,8,5,22,11,76,76,8,8,8,8,165,5,9,2 | 1080 | | 4,3,8,2,7,1,4,4,3,1,7,4,2,2,23,8,7,23,7,11,3,6,8,7,7,4,9,9,4,4,32,15,15,31,31,27,26,14,14,7,7,5,5,6,6,5,5,6,6,6,6,6,6,6,6,7,7,11,2,2,2,2,3,3,23,1,1,1,1,2,2,23,4,4,9,3,6,6,8,8,8,27,27,9,5,32,5,32,6,6,1,1,9,9,5,5,1,1,1,1,1,1,5,5,13,13,3,3,13,13,5,4,4,7,7,3,3,3,32,3,3,3,14,8,8,14,8,8,8,8,8,8,8,8,8,8,8,4,2,2,7,8,27,27,1,1,2,8,3,3,5,1,1,8 | 1081 | | 5,2,2,5,5,5,15,15,1,1 | 1083 | | 1,4,1,1,4,1,3,3,1,2,8,1,9,9,5,6,10,10,5,5,4,4,5,13,13,13,13,3,3,6,6,6,3,8,3,9,2,1,1,6 | 1084 | | 2,2 | 1085 | | 12,12,6,6,26,26,19,22,38,19,22,38,11,11 | 1086 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ SELECT seq, COUNT(*) FROM efis_film_festival GROUP BY seq; +-----+----------+ | seq | COUNT(*) | +-----+----------+ | 1 | 3102 | | 2 | 1620 | | 3 | 1120 | | 4 | 888 | | 5 | 765 | | 6 | 668 | | 7 | 592 | | 8 | 510 | | 9 | 469 | | 10 | 413 | | 11 | 371 | | 12 | 333 | | 13 | 308 | | 14 | 282 | | 15 | 272 | | 16 | 255 | | 17 | 246 | | 18 | 237 | | 19 | 233 | | 20 | 224 | | 21 | 210 | | 22 | 205 | | 23 | 197 | | 24 | 188 | | 25 | 179 | | 26 | 167 | | 27 | 159 | | 28 | 152 | | 29 | 150 | | 30 | 144 | | 31 | 139 | | 32 | 136 | | 33 | 135 | | 34 | 131 | | 35 | 122 | | 36 | 121 | | 37 | 116 | | 38 | 110 | | 39 | 108 | | 40 | 98 | | 41 | 95 | | 42 | 91 | | 43 | 86 | | 44 | 83 | | 45 | 81 | | 46 | 81 | | 47 | 78 | | 48 | 75 | | 49 | 73 | | 50 | 55 | | 51 | 53 | | 52 | 53 | | 53 | 53 | | 54 | 52 | | 55 | 50 | | 56 | 48 | | 57 | 47 | | 58 | 46 | | 59 | 43 | | 60 | 42 | | 61 | 42 | | 62 | 42 | Filmide kestused SELECT film_id, efis_film_physical_description.amount FROM efis_film_physical_description WHERE efis_film_physical_description.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION' ORDER BY film_id; SELECT film_id, efis_film_physical_description.amount FROM efis_film_physical_description WHERE efis_film_physical_description.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION' ORDER BY amount; SELECT efis_film.id, efis_film_physical_description.amount AS kogupikkus FROM efis_film_type_rel INNER JOIN efis_film ON efis_film_type_rel.film_id=efis_film.id INNER JOIN efis_film_physical_description ON efis_film.id=efis_film_physical_description.film_id WHERE efis_film_type_rel.film_type_id=1 AND efis_film_physical_description.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION' ORDER BY efis_film.id; SELECT efis_film.id, efis_film_physical_description.amount AS kogupikkus FROM efis_film_type_rel LEFT JOIN efis_film ON efis_film_type_rel.film_id=efis_film.id LEFT JOIN efis_film_physical_description ON efis_film.id=efis_film_physical_description.film_id WHERE efis_film_type_rel.film_type_id=67 AND efis_film_physical_description.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION' ORDER BY efis_film.id; SELECT efis_film.id, title_proper, amount, title FROM efis_film_type INNER JOIN efis_film_type_translation ON efis_film_type_translation.film_type_id=efis_film_type.id INNER JOIN efis_film_type_rel ON efis_film_type.id=efis_film_type_rel.film_type_id INNER JOIN efis_film ON efis_film.id=efis_film_type_rel.film_id INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id LEFT JOIN efis_film_physical_description ON efis_film_physical_description.film_id=efis_film.id AND (classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION" OR classificator_code2="FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION" OR classificator_code2="FILM_PHYSICAL_TYPE_FILE_FILE_DURATION") WHERE efis_film_translation.language='et' AND title="Täispikk mängufilm";