Benak.NET

CREATE TABLE teamy(
     id INTEGER AUTO_INCREMENT,
     nazev VARCHAR(64) NOT NULL,
     liga TINYINT,
     PRIMARY KEY (id)
) ENGINE = innodb;
CREATE TABLE zapasy (
     id INTEGER AUTO_INCREMENT,
     datumzapasu DATETIME NOT NULL,
     liga TINYINT,
     kolo TINYINT,
     teama INTEGER NOT NULL,
     teamb INTEGER NOT NULL,
     bodya TINYINT DEFAULT 0,
     bodyb TINYINT DEFAULT 0,
     PRIMARY KEY (id),
     FOREIGN KEY (teama) REFERENCES teamy(id),
     FOREIGN KEY (teamb) REFERENCES teamy(id)
) ENGINE = innodb;

SELECT ta.nazev AS nazeva, z.bodya, tb.nazev AS nazevb, z.bodyb
FROM zapasy AS z
INNER JOIN teamy AS ta ON z.teama = ta.id
INNER JOIN teamy AS tb ON z.teamb = tb.id
WHERE z.liga = 4 AND YEAR(z.datumzapasu) = '2006';