ERROR 1066 (42000): Not unique table/alias: 'mp' OUTER JOIN in mysql

Question

i have a problem converting my informix db to mysql. i got most things done but some functions just dont work.

DELIMITER //
CREATE PROCEDURE mw_getsvid(mwid INT) RETURNS INT
BEGIN
DECLARE svId INT;
SELECT sv.ID INTO svId
FROM messwert AS mw, messpunkt AS mp, mpzuordnung AS mpz, summvorschrift    AS sv
    LEFT JOIN messpunkt mp ON mw.messpunktid = mp.id
    LEFT JOIN mpzuordnung mpz ON mp.id = mpz.messpunktid
    LEFT JOIN summvorschrift sv ON mpz.summvorschriftid = sv.id
    LEFT JOIN messwert mw ON mpz.zeitraum_von <= mw.datendatum
    LEFT JOIN messwert mw ON mpz.zeitraum_bis > mw.datendatum;
UNION ALL
SELECT sv.ID INTO svId
FROM messwert AS mw, messpunkt AS mp, mpzuordnung AS mpz, summvorschrift AS sv
    RIGHT JOIN messpunkt mp ON mw.messpunktid = mp.id
    RIGHT JOIN mpzuordnung mpz ON mp.id = mpz.messpunktid
    RIGHT JOIN summvorschrift sv ON mpz.summvorschriftid = sv.id
    RIGHT JOIN messwert mw ON mpz.zeitraum_von <= mw.datendatum
    RIGHT JOIN messwert mw ON mpz.zeitraum_bis > mw.datendatum;
RETURN svId;



END //;

DELIMITER ;

this shows up: ERROR 1066 (42000): Not unique table/alias: 'mp'

btw this was the old one

CREATE PROCEDURE "informix".mw_getsvid(mwid INT)
RETURNING INT;  

DEFINE svId INT;
SELECT sv.ID INTO svId
    FROM MessWert mw, MessPunkt mp,
        OUTER (MPZuordnung mpz, SummVorschrift sv)
    WHERE mw.id = mwid
        AND mw.messpunktid = mp.id
        AND mp.id = mpz.messpunktid
        AND mpz.summvorschriftid = sv.id
        AND mpz.zeitraum_von <= mw.datendatum
        AND mpz.zeitraum_bis > mw.datendatum;

RETURN svId;

END PROCEDURE;

would be greate if u have a good thought on that :)

Answer 1

If you are going to use JOIN, you don't need to use all the tables in the FROM clause because you do duplicate job. If you are going to put the tables in the FROM clause and in the JOIN, you should use different aliases. You should do it this way:

DELIMITER //
CREATE FUNCTION mw_getsvid(mwid INT) RETURNS INT
BEGIN
DECLARE svId INT;
SELECT sv.ID INTO svId
FROM messwert AS mw
    LEFT JOIN messpunkt mp ON mw.messpunktid = mp.id
    LEFT JOIN mpzuordnung mpz ON mp.id = mpz.messpunktid
    LEFT JOIN summvorschrift sv ON mpz.summvorschriftid = sv.id
    LEFT JOIN messwert mw ON mpz.zeitraum_von <= mw.datendatum
    LEFT JOIN messwert mw ON mpz.zeitraum_bis > mw.datendatum;
UNION ALL
SELECT sv.ID INTO svId
FROM messwert AS mw
    RIGHT JOIN messpunkt mp ON mw.messpunktid = mp.id
    RIGHT JOIN mpzuordnung mpz ON mp.id = mpz.messpunktid
    RIGHT JOIN summvorschrift sv ON mpz.summvorschriftid = sv.id
    RIGHT JOIN messwert mw ON mpz.zeitraum_von <= mw.datendatum
    RIGHT JOIN messwert mw ON mpz.zeitraum_bis > mw.datendatum;
RETURN svId;

END //;

DELIMITER ;