CODEWITHSUNDEEP
mysqlsqlview
BadAtPHP
BadAtPHP

Reputation: 102

Creating a view in SQL with three tables

I wanted to make a view with all records from the 3 tables I have.. But I'm getting an error and I don't know how to solve it.

The error is that Customer.customerID is duplicated.

Create view overview as
Select
customer.customerID,
customer.username, 
customer.name, 
customer.surname, 
customer.city, 
customer.street, 
customer.zipcode, 
customer.birthdate, 
customer.`payment method`,
customer.screenname,
order.OrderID,
order.customerID,
order.date,
order.GameID,
games.GameID,
games.game,
games.price,
games.released,
games.genre,
games.grid
FROM customer
INNER JOIN order
ON customer.customerID = order.customerID
INNER JOIN games
ON games.GameID = order.GameID

The tables and their rows:

order OrderID, customerID, date, GameID

games GameID, game, price, released, genre, grid

customer customerID, username, name, surname, city, street, zipcode, birthdate, payment method, screenname

please help.

Upvotes: 0

Views: 152

Answers (4)

BadAtPHP
BadAtPHP

Reputation: 102

I removed the double named rows like @Mahmoud_Gamal suggested but it gave me an issue with SQL making a record with every combination possible from the data in other records like:

Customer 1 | Order1 | Game1

Customer 2 | Order1 | Game1

Customer 1 | Order2 | Game2

I solved it by using "select distinct", never though it would give soo much pain by forgetting to use the distinct function...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You have duplicate columns in the select. In addition to CustomerId, you also have GameId. Just list each column once:

Select
    customer.customerID,
    customer.username, 
    customer.name, 
    customer.surname, 
    customer.city, 
    customer.street, 
    customer.zipcode, 
    customer.birthdate, 
    customer.`payment method`,
    customer.screenname,
    `order`.OrderID,
    `order`.date,
    `order`.GameID,
    games.game,
    games.price,
    games.released,
    games.genre,
    games.grid

The duplicates are unnecessary because the join conditions would just make them the same anyway.

As a note; order is a very bad name for a table because it is a reserved word. You should avoid using reserved words for table and column names. These then need to be escaped in queries, which just clutters the queries.

By the way, for this type of query, if you want all the columns from the three tables without duplicates, you can use * with the using clause:

Create view overview as Select * FROM customer INNER JOIN order USING (CustomerId) INNER JOIN games USING (GameID)

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

The CustomerID column is selected twice with the same name, you have to give one of them a different name using alias AS ... like this:

Create view overview as
Select
customer.customerID,
customer.username, 
customer.name, 
customer.surname, 
customer.city, 
customer.street, 
customer.zipcode, 
customer.birthdate, 
customer.`payment method`,
customer.screenname,
order.OrderID,
order.customerID AS OrderCustomerId, -- this
order.date,
order.GameID,
games.GameID gamesGameID,           -- This too
games.game,
games.price,
games.released,
games.genre,
games.grid
FROM customer
INNER JOIN order
ON customer.customerID = order.customerID
INNER JOIN games  ON games.GameID = order.GameID

Or remove it from the select as it is the same value as the Customers.CustomerId.

Upvotes: 1

Adrian Nasui
Adrian Nasui

Reputation: 1095

You have two columns in your result set that are named the same, CustomerId. Try this:

Create view overview as
Select
customer.customerID as CustomerId1,
customer.username, 
customer.name, 
customer.surname, 
customer.city, 
customer.street, 
customer.zipcode, 
customer.birthdate, 
customer.`payment method`,
customer.screenname,
order.OrderID,
order.customerID as CustomerId2,
order.date,
order.GameID,
games.GameID,
games.game,
games.price,
games.released,
games.genre,
games.grid
FROM customer
INNER JOIN order
ON customer.customerID = order.customerID
INNER JOIN games
ON games.GameID = order.GameID

Upvotes: 0

Related Questions