Create view : MySQL Inner join aliases

Question

I am trying to create a view in MySQL, however, an error occured: Duplicate column name 'profil_id'

This is my request:

CREATE VIEW my_view
AS SELECT * FROM `profils` AS `p` 
INNER JOIN `table_A` ON table_A.profil_id = p.id 
INNER JOIN `table_B` ON table_B.profil_id = p.id
WHERE p.id = '1';

I know this is because I have to create an alias for profil_id, but I don't know how to figure out that with an INNER JOIN...

INNER JOIN `table_A` ON table_A.profil_id = p.id AS table_A_profil_id

Does not work.

Thank you for your help.

Answer 1

You can use the following:

CREATE VIEW my_view AS
    SELECT `p`.*, `table_A`.`profil_id` AS table_A_profil_id, `table_B`.`profil_id` AS table_B_profil_id
    FROM `profils` AS `p` 
        INNER JOIN `table_A` ON table_A.profil_id = p.id 
        INNER JOIN `table_B` ON table_B.profil_id = p.id
    WHERE p.id = '1';

You can't use * in this case because you INNER JOIN two tables with the same column name. You need to list the columns for these two tables and set an alias to the duplicate column names.