Error using Inner Join in MYSQL

Question

I’m using the INNER JOIN mysql to merge and display data.

Tables and data insertion:

CREATE TABLE EXPERIENCIA(
     exp_pri INT NOT NULL AUTO_INCREMENT,
     experiencia VARCHAR(100),         
     PRIMARY KEY(exp_pri)
);

CREATE TABLE PRANCHA(
    prancha_pri INT NOT NULL AUTO_INCREMENT,
    tipo_prancha VARCHAR(15),
    tamanho_prancha VARCHAR(8),
    meio_prancha VARCHAR(2),
    litragem_prancha VARCHAR(3),
    PRIMARY KEY (prancha_pri)
);

CREATE TABLE ALTURAEPESO(
    idAltPes INT NOT NULL AUTO_INCREMENT,
    idExp INT,
    idPrancha INT,
    altura VARCHAR(4),
    peso VARCHAR(3),
    primary key (idAltPes),
    constraint fk_idExp foreign key (idExp) references EXPERIENCIA (exp_pri),
    constraint fk_idPrancha foreign key (idPrancha) references PRANCHA (prancha_pri)
 );    


INSERT INTO EXPERIENCIA VALUES (NULL, 'INICIANTE');
INSERT INTO EXPERIENCIA VALUES (NULL, 'INICIANTE');
INSERT INTO EXPERIENCIA VALUES (NULL, 'AVANÇADO');

INSERT INTO PRANCHA VALUES (NULL, 'FUNBOARD', '8 PES', '21', '43L');
INSERT INTO PRANCHA VALUES (NULL, 'LONGBOARD', '8.8 PES', '20', '55L');
INSERT INTO PRANCHA VALUES (NULL, 'PRANCHA', ' 6 PES', '21', '45L');

INSERT INTO ALTURAEPESO VALUES (NULL, 1, 1, '1.90', '70');
INSERT INTO ALTURAEPESO VALUES (NULL, 2, 2, '1.70', '90');
INSERT INTO ALTURAEPESO VALUES (NULL, 3, 3, '1.60', '65');

Script:

SELECT EXP.experiencia,
       AEP.altura,
       AEP.peso,
       PRAN.tipo_prancha,
       PRAN.tamanho_prancha,
       PRAN.meio_prancha,
       PRAN.litragem_prancha
FROM EXPERIENCIA AS EXP
    INNER JOIN PRANCHA AS PRAN ON 
    (PRAN.prancha_pri = AEP.prancha_pri)
    INNER JOIN ALTURAEPESO AS AEP ON 
    (EXP.exp_pri = AEP.exp_pri)

Mysql is showing the following error:

Unknown column 'AEP.prancha_pri' in 'on clause'

How can I fix this?

Answer 1

Your first Inner Join refers to a table that is only called in the second Inner Join...

Try it this way:

SELECT EXP.experiencia,
       AEP.altura,
       AEP.peso,
       PRAN.tipo_prancha,
       PRAN.tamanho_prancha,
       PRAN.meio_prancha,
       PRAN.litragem_prancha
FROM EXPERIENCIA AS EXP
    INNER JOIN ALTURAEPESO AS AEP ON 
    (EXP.exp_pri = AEP.exp_pri)
    INNER JOIN PRANCHA AS PRAN ON 
    (PRAN.prancha_pri = AEP.prancha_pri)

Answer 2

I got!

SELECT EXP.experiencia,
       AEP.altura,
       AEP.peso,
       PRAN.tipo_prancha,
       PRAN.tamanho_prancha,
       PRAN.meio_prancha,
       PRAN.litragem_prancha
FROM EXPERIENCIA AS EXP
       INNER JOIN ALTURAEPESO AS AEP ON 
       (EXP.exp_pri = AEP.idAltPes)
       INNER JOIN PRANCHA AS PRAN ON 
       (PRAN.prancha_pri = AEP.idAltPes)

The problem was the reference I was giving to the AlturaePeso - AEP.

I was calling the id of Experiencia and not his own, AlturaePeso rsrs.