CODEWITHSUNDEEP
mysqlsql
Clint
Clint

Reputation: 93

Why do I get the errors ambiguous field or unknown column in field list?

I created these two tables:

CREATE TABLE `Game` (

 `GameName` varchar(30) NOT NULL,
 `GameGenre` varchar(20) NOT NULL,
 `GameDescription` varchar(500) NOT NULL,
 `GameMode` varchar(20) NOT NULL,
 PRIMARY KEY (`GameName`)
) ENGINE=InnoDB;

CREATE TABLE `GameScoreTotal` (

 `ScoreID` int(3) NOT NULL AUTO_INCREMENT,
 `GameName` varchar(30) NOT NULL,
 `OverallScoreTotal` mediumint(9) NOT NULL,
 `GraphicsTotal` mediumint(9) NOT NULL,
 `StoryTotal` mediumint(9) NOT NULL,
 `GameplayTotal` mediumint(9) NOT NULL,
 `TimeTotal` mediumint(9) NOT NULL,
 PRIMARY KEY (`ScoreID`),
  CONSTRAINT  FOREIGN KEY (`GameName`) REFERENCES `Game` (`GameName`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

My query needs to return all information from game table for the top ten games based on GameScoreTotal.OverallScoreTotal.

Queries I have tried:

SELECT GameName 
FROM Game 
FULL JOIN GameScoreTotal 
    ON Game.GameName=GameScoreTotal.GameName 
ORDER BY OverallScoreTotal DESC 
LIMIT 0, 10;

SELECT Game.GameName 
FROM Game 
FULL JOIN GameScoreTotal 
    ON Game.GameName=GameScoreTotal.GameName 
ORDER BY OverallScoreTotal DESC 
LIMIT 0, 10;

Both tables have legitimate records within them.

Upvotes: 0

Views: 838

Answers (1)

user756519
user756519

Reputation:

Query 1

In your first query, the issue is that the field GameName exist in both the tables Game and GameScoreTotal. Hence the error Column 'GameName' in field list is ambiguous

Query 2

MySQL doesn't have a FULL JOIN. Read here about MySQL JOIN Syntax.

Try the LEFT OUTER JOIN between Game and GameScoreTotal table, this join will fetch all records from Game table and all the matching records from GameScoreTotal table, if there are no matching records in GameScoreTotal table, NULL will be displayed.

You can also try INNER JOIN, which will fetch only the matching records between the two tables.

Script:

SELECT Game.GameName 
FROM Game 
LEFT OUTER JOIN GameScoreTotal 
    ON Game.GameName=GameScoreTotal.GameName 
ORDER BY OverallScoreTotal DESC 
LIMIT 0, 10;

Upvotes: 1

Related Questions