I am using INNER JOIN
in mysql to merge and display the 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 displaying the following error:
Unknown column 'AEP.prancha_pri' in 'on clause'
How can I resolve this?