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 :)

`outer join is a explizit join but you have also comma separated tables where the join is formulated in the where clause. That do not work — Jens, Aug 02 '17 at 12:24

nacho · Accepted Answer · 2017-08-02T11:43:30.527

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 ;

now it shows an different syntax error ^^ ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT BEGIN DECLARE svId INT; SELECT sv.ID INTO svId FROM messwert AS mw LEFT ' at line 1 — itsme, Aug 02 '17 at 11:23
@itsme Sorry, i just copied&pasted from your code, try it now. If you need a value returned, you must use FUNCTION, otherwise you can use PROCEDURE — nacho, Aug 02 '17 at 11:46

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

1 Answers1