SHOW:
|
|
- or go back to the newest paste.
| 1 | --- ALÁBBI lekérdezése | |
| 2 | ||
| 3 | SELECT tcmt1.csb_team_id AS team_id1, tcmt2.csb_team_id AS team_id2, tcm.id AS match_id | |
| 4 | FROM `ttnsc_csb_match` AS tcm | |
| 5 | INNER JOIN `ttnsc_csb_match_team` AS tcmt1 ON tcm.id = tcmt1.csb_match_id | |
| 6 | INNER JOIN `ttnsc_csb_match_team` AS tcmt2 ON tcm.id = tcmt2.csb_match_id | |
| 7 | AND tcmt1.csb_team_id != tcmt2.csb_team_id | |
| 8 | GROUP BY tcm.id | |
| 9 | ||
| 10 | ---------------------------------------------------- | |
| 11 | ||
| 12 | -- | |
| 13 | -- Tábla szerkezet: `ttnsc_csb_match` | |
| 14 | -- | |
| 15 | ||
| 16 | CREATE TABLE IF NOT EXISTS `ttnsc_csb_match` ( | |
| 17 | `id` int(5) NOT NULL AUTO_INCREMENT, | |
| 18 | PRIMARY KEY (`id`) | |
| 19 | ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; | |
| 20 | ||
| 21 | -- | |
| 22 | -- A tábla adatainak kiíratása `ttnsc_csb_match` | |
| 23 | -- | |
| 24 | ||
| 25 | INSERT INTO `ttnsc_csb_match` (`id`) VALUES | |
| 26 | (10), | |
| 27 | (11); | |
| 28 | ||
| 29 | -- -------------------------------------------------------- | |
| 30 | ||
| 31 | -- | |
| 32 | -- Tábla szerkezet: `ttnsc_csb_match_team` | |
| 33 | -- | |
| 34 | ||
| 35 | CREATE TABLE IF NOT EXISTS `ttnsc_csb_match_team` ( | |
| 36 | `csb_team_id` int(5) NOT NULL, | |
| 37 | `csb_match_id` int(5) NOT NULL, | |
| 38 | UNIQUE KEY `csb_team_id` (`csb_team_id`,`csb_match_id`) | |
| 39 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; | |
| 40 | ||
| 41 | -- | |
| 42 | -- A tábla adatainak kiíratása `ttnsc_csb_match_team` | |
| 43 | -- | |
| 44 | ||
| 45 | INSERT INTO `ttnsc_csb_match_team` (`csb_team_id`, `csb_match_id`) VALUES | |
| 46 | (1, 10), | |
| 47 | (1, 11), | |
| 48 | (2, 10), | |
| 49 | (2, 11); | |
| 50 | ||
| 51 | -- -------------------------------------------------------- | |
| 52 | ||
| 53 | -- | |
| 54 | -- Tábla szerkezet: `ttnsc_csb_team` | |
| 55 | -- | |
| 56 | ||
| 57 | CREATE TABLE IF NOT EXISTS `ttnsc_csb_team` ( | |
| 58 | `id` int(5) NOT NULL AUTO_INCREMENT, | |
| 59 | `name` varchar(45) NOT NULL, | |
| 60 | PRIMARY KEY (`id`) | |
| 61 | ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; | |
| 62 | ||
| 63 | -- | |
| 64 | -- A tábla adatainak kiíratása `ttnsc_csb_team` | |
| 65 | -- | |
| 66 | ||
| 67 | INSERT INTO `ttnsc_csb_team` (`id`, `name`) VALUES | |
| 68 | (1, 'team1'), | |
| 69 | (2, 'team2'), | |
| 70 | (3, 'team3'), | |
| 71 | (4, 'team4'); | |
| 72 | ||
| 73 | -- | |
| 74 | -- Megkötések a kiírt táblákhoz | |
| 75 | -- | |
| 76 | ||
| 77 | -- | |
| 78 | -- Megkötések a táblához `ttnsc_csb_match_team` | |
| 79 | -- | |
| 80 | ALTER TABLE `ttnsc_csb_match_team` | |
| 81 | ADD CONSTRAINT `ttnsc_csb_match_team_ibfk_1` FOREIGN KEY (`csb_team_id`) REFERENCES `ttnsc_csb_team` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; |
