CODEWITHSUNDEEP
sqlphpmyadmin
Stuxnet
Stuxnet

Reputation: 103

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'

Upvotes: 0

Views: 1710

Answers (2)

scgough
scgough

Reputation: 5252

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.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

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';

Upvotes: 0

Related Questions