/* Soccer */
DELETE FROM schools_officials_associations WHERE activity IN ('BSC', 'GSC');

INSERT INTO schools_officials_associations (school,officials_association,activity) VALUES

/* Blue Mountain Soccer */
(17,33,'BSC'),
(105,33,'BSC'),
(248,33,'BSC'),
(17,33,'GSC'),
(105,33,'GSC'),
(248,33,'GSC'),

/* Central Oregon Soccer */
(46,34,'BSC'),
(48,34,'BSC'),
(260,34,'BSC'),
(522,34,'BSC'),
(368,34,'BSC'),
(100,34,'BSC'),
(186,34,'BSC'),
(135,34,'BSC'),
(47,34,'BSC'),
(106,34,'BSC'),
(142,34,'BSC'),
(46,34,'GSC'),
(48,34,'GSC'),
(260,34,'GSC'),
(522,34,'GSC'),
(368,34,'GSC'),
(100,34,'GSC'),
(186,34,'GSC'),
(135,34,'GSC'),
(47,34,'GSC'),
(106,34,'GSC'),
(142,34,'GSC'),

/* Coos/Brookings Soccer */
(159,35,'BSC'),
(230,35,'BSC'),
(221,35,'BSC'),
(65,35,'BSC'),
(132,35,'BSC'),
(71,35,'BSC'),
(18,35,'BSC'),
(159,35,'GSC'),
(230,35,'GSC'),
(221,35,'GSC'),
(65,35,'GSC'),
(132,35,'GSC'),
(71,35,'GSC'),
(18,35,'GSC'),

/* Cowlitx County Soccer */
(103,119,'BSC'),
(110,119,'BSC'),
(103,119,'GSC'),
(110,119,'GSC'),

/* Douglas County Soccer */
(72,36,'BSC'),
(3,36,'BSC'),
(165,36,'BSC'),
(22,36,'BSC'),
(9,36,'BSC'),
(258,36,'BSC'),
(174,36,'BSC'),
(201,36,'BSC'),
(120,36,'BSC'),
(189,36,'BSC'),
(72,36,'GSC'),
(3,36,'GSC'),
(165,36,'GSC'),
(22,36,'GSC'),
(9,36,'GSC'),
(258,36,'GSC'),
(174,36,'GSC'),
(201,36,'GSC'),
(120,36,'GSC'),
(189,36,'GSC'),

/* Hermiston Soccer */
(182,37,'BSC'),
(123,37,'BSC'),
(252,37,'BSC'),
(21,37,'BSC'),
(61,37,'BSC'),
(73,37,'BSC'),
(404,37,'BSC'),
(109,37,'BSC'),
(330,37,'BSC'),
(182,37,'GSC'),
(123,37,'GSC'),
(252,37,'GSC'),
(21,37,'GSC'),
(61,37,'GSC'),
(73,37,'GSC'),
(404,37,'GSC'),
(109,37,'GSC'),
(330,37,'GSC'),

/* Klamath Falls Soccer */
(245,38,'BSC'),
(116,38,'BSC'),
(115,38,'BSC'),
(181,38,'BSC'),
(245,38,'GSC'),
(116,38,'GSC'),
(115,38,'GSC'),
(181,38,'GSC'),

/* Lane County Soccer */
(263,39,'BSC'),
(85,39,'BSC'),
(269,39,'BSC'),
(344,39,'BSC'),
(227,39,'BSC'),
(251,39,'BSC'),
(257,39,'BSC'),
(265,39,'BSC'),
(232,39,'BSC'),
(113,39,'BSC'),
(214,39,'BSC'),
(215,39,'BSC'),
(183,39,'BSC'),
(41,39,'BSC'),
(263,39,'GSC'),
(85,39,'GSC'),
(269,39,'GSC'),
(344,39,'GSC'),
(227,39,'GSC'),
(251,39,'GSC'),
(257,39,'GSC'),
(265,39,'GSC'),
(232,39,'GSC'),
(113,39,'GSC'),
(214,39,'GSC'),
(215,39,'GSC'),
(183,39,'GSC'),
(41,39,'GSC'),

/* Mid-Columbia County Soccer */
(81,40,'BSC'),
(409,40,'BSC'),
(196,40,'BSC'),
(81,40,'GSC'),
(409,40,'GSC'),
(196,40,'GSC'),

/* Mid-Valley Soccer */
(173,41,'BSC'),
(12,41,'BSC'),
(13,41,'BSC'),
(202,41,'BSC'),
(250,41,'BSC'),
(172,41,'BSC'),
(256,41,'BSC'),
(177,41,'BSC'),
(185,41,'BSC'),
(63,41,'BSC'),
(268,41,'BSC'),
(133,41,'BSC'),
(16,41,'BSC'),
(173,41,'GSC'),
(12,41,'GSC'),
(13,41,'GSC'),
(202,41,'GSC'),
(250,41,'GSC'),
(172,41,'GSC'),
(256,41,'GSC'),
(177,41,'GSC'),
(185,41,'GSC'),
(63,41,'GSC'),
(268,41,'GSC'),
(133,41,'GSC'),
(16,41,'GSC'),

/* North Coast Soccer */
(53,42,'BSC'),
(99,42,'BSC'),
(188,42,'BSC'),
(53,42,'GSC'),
(99,42,'GSC'),
(188,42,'GSC'),

/* MISOA (Portland) Soccer */
(92,43,'BSC'),
(30,43,'BSC'),
(95,43,'BSC'),
(145,43,'BSC'),
(76,43,'BSC'),
(126,43,'BSC'),
(226,43,'BSC'),
(279,43,'BSC'),
(6,43,'BSC'),
(144,43,'BSC'),
(233,43,'BSC'),
(98,43,'BSC'),
(153,43,'BSC'),
(239,43,'BSC'),
(152,43,'BSC'),
(29,43,'BSC'),
(337,43,'BSC'),
(247,43,'BSC'),
(37,43,'BSC'),
(38,43,'BSC'),
(150,43,'BSC'),
(151,43,'BSC'),
(4,43,'BSC'),
(68,43,'BSC'),
(52,43,'BSC'),
(149,43,'BSC'),
(125,43,'BSC'),
(326,43,'BSC'),
(266,43,'BSC'),
(93,43,'BSC'),
(27,43,'BSC'),
(205,43,'BSC'),
(111,43,'BSC'),
(94,43,'BSC'),
(146,43,'BSC'),
(246,43,'BSC'),
(249,43,'BSC'),
(387,43,'BSC'),
(7,43,'BSC'),
(89,43,'BSC'),
(8,43,'BSC'),
(55,43,'BSC'),
(156,43,'BSC'),
(210,43,'BSC'),
(209,43,'BSC'),
(231,43,'BSC'),
(26,43,'BSC'),
(238,43,'BSC'),
(79,43,'BSC'),
(270,43,'BSC'),
(225,43,'BSC'),
(370,43,'BSC'),
(417,43,'BSC'),
(108,43,'BSC'),
(119,43,'BSC'),
(212,43,'BSC'),
(75,43,'BSC'),
(277,43,'BSC'),
(194,43,'BSC'),
(23,43,'BSC'),
(321,43,'BSC'),
(338,43,'BSC'),
(287,43,'BSC'),
(112,43,'BSC'),
(90,43,'BSC'),
(366,43,'BSC'),
(339,43,'BSC'),
(92,43,'GSC'),
(30,43,'GSC'),
(95,43,'GSC'),
(145,43,'GSC'),
(76,43,'GSC'),
(126,43,'GSC'),
(226,43,'GSC'),
(279,43,'GSC'),
(6,43,'GSC'),
(144,43,'GSC'),
(233,43,'GSC'),
(98,43,'GSC'),
(153,43,'GSC'),
(239,43,'GSC'),
(152,43,'GSC'),
(29,43,'GSC'),
(337,43,'GSC'),
(247,43,'GSC'),
(37,43,'GSC'),
(38,43,'GSC'),
(150,43,'GSC'),
(151,43,'GSC'),
(4,43,'GSC'),
(68,43,'GSC'),
(52,43,'GSC'),
(149,43,'GSC'),
(125,43,'GSC'),
(326,43,'GSC'),
(266,43,'GSC'),
(93,43,'GSC'),
(27,43,'GSC'),
(205,43,'GSC'),
(111,43,'GSC'),
(94,43,'GSC'),
(146,43,'GSC'),
(246,43,'GSC'),
(249,43,'GSC'),
(387,43,'GSC'),
(7,43,'GSC'),
(89,43,'GSC'),
(8,43,'GSC'),
(55,43,'GSC'),
(156,43,'GSC'),
(210,43,'GSC'),
(209,43,'GSC'),
(231,43,'GSC'),
(26,43,'GSC'),
(238,43,'GSC'),
(79,43,'GSC'),
(270,43,'GSC'),
(225,43,'GSC'),
(370,43,'GSC'),
(417,43,'GSC'),
(108,43,'GSC'),
(119,43,'GSC'),
(212,43,'GSC'),
(75,43,'GSC'),
(277,43,'GSC'),
(194,43,'GSC'),
(23,43,'GSC'),
(321,43,'GSC'),
(338,43,'GSC'),
(287,43,'GSC'),
(112,43,'GSC'),
(90,43,'GSC'),
(366,43,'GSC'),
(339,43,'GSC'),

/* Salem Soccer */
(129,44,'BSC'),
(24,44,'BSC'),
(131,44,'BSC'),
(130,44,'BSC'),
(128,44,'BSC'),
(127,44,'BSC'),
(372,44,'BSC'),
(155,44,'BSC'),
(139,44,'BSC'),
(244,44,'BSC'),
(121,44,'BSC'),
(253,44,'BSC'),
(57,44,'BSC'),
(42,44,'BSC'),
(83,44,'BSC'),
(36,44,'BSC'),
(273,44,'BSC'),
(54,44,'BSC'),
(158,44,'BSC'),
(262,44,'BSC'),
(141,44,'BSC'),
(193,44,'BSC'),
(97,44,'BSC'),
(441,44,'BSC'),
(363,44,'BSC'),
(129,44,'GSC'),
(24,44,'GSC'),
(131,44,'GSC'),
(130,44,'GSC'),
(128,44,'GSC'),
(127,44,'GSC'),
(372,44,'GSC'),
(155,44,'GSC'),
(139,44,'GSC'),
(244,44,'GSC'),
(121,44,'GSC'),
(253,44,'GSC'),
(57,44,'GSC'),
(42,44,'GSC'),
(83,44,'GSC'),
(36,44,'GSC'),
(273,44,'GSC'),
(54,44,'GSC'),
(158,44,'GSC'),
(262,44,'GSC'),
(141,44,'GSC'),
(193,44,'GSC'),
(97,44,'GSC'),
(441,44,'GSC'),
(363,44,'GSC'),

/* Southern Oregon Soccer */
(224,45,'BSC'),
(51,45,'BSC'),
(50,45,'BSC'),
(222,45,'BSC'),
(204,45,'BSC'),
(154,45,'BSC'),
(34,45,'BSC'),
(33,45,'BSC'),
(178,45,'BSC'),
(78,45,'BSC'),
(35,45,'BSC'),
(118,45,'BSC'),
(349,45,'BSC'),
(224,45,'GSC'),
(51,45,'GSC'),
(50,45,'GSC'),
(222,45,'GSC'),
(204,45,'GSC'),
(154,45,'GSC'),
(34,45,'GSC'),
(33,45,'GSC'),
(178,45,'GSC'),
(78,45,'GSC'),
(35,45,'GSC'),
(118,45,'GSC'),
(349,45,'GSC'),

/* Southwest Idaho */
(66,116,'BSC'),
(136,116,'BSC'),
(66,116,'GSC'),
(136,116,'GSC');


/* Get confirmation of the entries */
SELECT 
	DISTINCT(S.short_name),
	CS.classification,
	OA.short_name

FROM
	schools AS S,
	officials_associations AS OA,
	schools_officials_associations AS SOA,
	classification_schools AS CS

WHERE
	SOA.school = S.id AND
	SOA.officials_association = OA.id AND
	SOA.activity IN ('BSC', 'GSC') AND
	CS.school = S.id AND
	CS.school_year = 2015

ORDER BY
	OA.short_name,
	CS.classification DESC,
	S.short_name


