Problem in select

Question

How to solve this?

create table tbl_prescricao(
id_prescricao int not null primary key auto_increment,
tipo_prescricao varchar(250) not null,
valor_prescricao decimal(10,2)
);

create table tbl_juntaprescricao(
id_junta int not null primary key auto_increment,
prescricao int not null,
constraint fk_prescricao foreign key (prescricao) references tbl_prescricao 
(id_prescricao),
prescricao2 int,
constraint fk_prescricao2 foreign key (prescricao2) references 
tbl_prescricao (id_prescricao)
);

insert into tbl_prescricao values
(1, 'Remédio 1', 59.00),
(2, 'Remédio 2', 80.00),
(3, 'Remédio 3', null);

insert into tbl_juntaprescricao values (1, 1, 2), (2, 3, null);

select tipo_prescricao from tbl_juntaprescricao as J
inner join tbl_prescricao as P on J.prescricao = P.id_prescricao
inner join tbl_prescricao as P on J.prescricao2 = P.id_prescricao;

While doing select error occurs: Error Code: 1066. Not Unique table/alias: 'P'

I need him to bring the name of the drugs in the table tbl_joint order.

If I wasn’t specific about the problem ask me more details.

Answer 1

The alias has to be unique. On the last lines make the following change:

select P.tipo_prescricao, P2.tipo_prescricao from tbl_juntaprescricao as J
inner join tbl_prescricao as P on J.prescricao = P.id_prescricao
inner join tbl_prescricao as P2 on J.prescricao2 = P2.id_prescricao;