Administrace komentářů

ALTER TABLE polls MODIFY id_candidate1 NULL
/

ALTER TABLE polls MODIFY id_candidate2 NULL
/

ALTER TABLE polls MODIFY id_candidate3 NULL
/

ALTER TABLE polls MODIFY id_candidate4 NULL
/

UPDATE polls p
SET
    p.id_candidate1 = NULL
WHERE
    NOT EXISTS (SELECT
            1
        FROM
            candidates cc
        WHERE
            cc.id = p.id_candidate1)
/

UPDATE polls p
SET
    p.id_candidate2 = NULL
WHERE
    NOT EXISTS (SELECT
            1
        FROM
            candidates cc
        WHERE
            cc.id = p.id_candidate2)
/

UPDATE polls p
SET
    p.id_candidate3 = NULL
WHERE
    NOT EXISTS (SELECT
            1
        FROM
            candidates cc
        WHERE
            cc.id = p.id_candidate3)
/

UPDATE polls p
SET
    p.id_candidate4 = NULL
WHERE
    NOT EXISTS (SELECT
            1
        FROM
            candidates cc
        WHERE
            cc.id = p.id_candidate4)
/

COMMIT
/

ALTER TABLE candidates ADD CONSTRAINT pk_candidates PRIMARY KEY (id)
/

ALTER TABLE polls ADD CONSTRAINT fk_vote_candidate1 FOREIGN KEY (id_candidate1) REFERENCES candidates (id)
/

ALTER TABLE polls ADD CONSTRAINT fk_vote_candidate2 FOREIGN KEY (id_candidate2) REFERENCES candidates (id)
/

ALTER TABLE polls ADD CONSTRAINT fk_vote_candidate3 FOREIGN KEY (id_candidate3) REFERENCES candidates (id)
/

ALTER TABLE polls ADD CONSTRAINT fk_vote_candidate4 FOREIGN KEY (id_candidate4) REFERENCES candidates (id)
/

SELECT
    c1.prijmeni AS candidate1_surname,
    p.votes1 AS candidate1_votes,
    c2.prijmeni AS candidate2_surname,
    p.votes2 AS candidate2_votes,
    c3.prijmeni AS candidate3_surname,
    p.votes3 AS candidate3_votes,
    c4.prijmeni AS candidate4_surname,
    p.votes4 AS candidate4_votes
FROM
    polls p
        LEFT JOIN candidates c1 ON (p.id_candidate1 = c1.id)
        LEFT JOIN candidates c2 ON (p.id_candidate2 = c2.id)
        LEFT JOIN candidates c3 ON (p.id_candidate3 = c3.id)
        LEFT JOIN candidates c4 ON (p.id_candidate4 = c4.id)
/