phpmyadmin Question posted in
The official documentation can be found here.

Creating view in phpmyadmin

When i try to create view like this CREATE VIEW data2tables AS

SELECT * FROM Employees e INNER JOIN Computers c ON e.id = c.id WHERE e.name = 'Georgi' AND c.department = 'Sales'

it gives me error saying that #1060 – Duplicate column name ‘id’,and i have no idea how to fix it or why does the error occur.

CREATE VIEW data2tables AS 
SELECT * FROM Employees e INNER JOIN Computers c 
ON e.id = c.id 
WHERE e.name = 'Georgi' AND c.department = 'Sales';

#1060 – Duplicate column name ‘id’

Tags:

2

Answers


  1. 0

    try to specify columns for Computer’s table.

    CREATE VIEW data2tables AS 
SELECT e.*, c.[column_name].... FROM Employees e INNER JOIN Computers c 
ON e.id = c.id 
WHERE e.name = 'Georgi' AND c.department = 'Sales';
    Login or Signup to reply.
  2. 0

    Your table Employees and the table Computers both have a column named id.

    When you say SELECT * FROM Employees e INNER JOIN Computers c... you are basically saying give me every column ‘Employees’ AND ‘Computers’ so you are getting a duplicate of id (and any other column in there that may be the same name).

    With a VIEW you want to select a defined set of results so I would recommend explicitly specifying the columns you want from both tables.

    If you require id from both tables I would recommend giving the joined table (Computers) id column an alias…something like this:

    CREATE VIEW data2tables AS 
SELECT e.id, e.fieldA, e.fieldN, c.id as ComputersId, c.fieldA, c.fieldN 
FROM Employees e 
INNER JOIN Computers c ON e.id = c.id 
WHERE e.name = 'Georgi' AND c.department = 'Sales';

    That method of aliasing will also apply to any other cross-over column names you encounter.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search