Page 1 of 1

problem with ambiguous field names (mysql 4.1)

Posted: Wed Feb 07, 2007 4:38 pm
by jolinar
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Does anyone know how to get around the "ambiguous field" bug in MySQL 4.1?

The nature of the beast is as follows, when executing the following query:

[syntax="sql"]SELECT comment_id,comment,date,user_name,title FROM ((blog_comment INNER JOIN users USING (user_id)) INNER JOIN blog_entry USING(blog_id)) ORDER BY date DESC;
The following error is returned:
Column 'date' in field list is ambiguous
Does anyone know any workarounds for this problem? (It certainly is an annoying one, though I'm probably overlooking something simple)

If needed, the structure of the tables concerned is as follows: (lifted from mysqladmin backup)

Code: Select all

CREATE TABLE  `sobek`.`blog_comment` (
  `comment_ID` int(10) unsigned NOT NULL auto_increment,
  `blog_ID` int(10) unsigned NOT NULL,
  `user_ID` int(10) unsigned NOT NULL,
  `comment` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`comment_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `sobek`.`blog_entry` (
  `blog_ID` int(10) unsigned NOT NULL auto_increment,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`blog_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `sobek`.`users` (
  `user_name` varchar(45) NOT NULL,
  `pass` varchar(60) NOT NULL,
  `real_name` varchar(45) NOT NULL,
  `user_level` int(10) unsigned NOT NULL,
  `user_email` varchar(45) NOT NULL,
  `user_date_joined` datetime NOT NULL,
  `user_ID` int(10) unsigned NOT NULL auto_increment,
  `last_login` datetime NOT NULL,
  PRIMARY KEY  (`user_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB';

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Feb 07, 2007 6:57 pm
by AKA Panama Jack
It's not ambiguous, it's a reserved word.

You shouldn't use reserved words as field names for starters but if you absolutely MUST use them then they have to be backticked.

Code: Select all

SELECT comment_id, comment, `date`, user_name, title FROM ((blog_comment INNER JOIN users USING (user_id)) INNER JOIN blog_entry USING(blog_id)) ORDER BY `date` DESC;
You can get away without backticking date in the SELECT area but it must be backticked in the ORDER BY with the way you are using the query. Honestly you shouldn't be using date as a field anyway. If this is your own project you should rename those fields to something different.

http://dev.mysql.com/doc/refman/4.1/en/ ... words.html

Posted: Wed Feb 07, 2007 7:24 pm
by jolinar
Thanks :D "AKA Panama Jack"

Since it's my own project I'll rename the cols in question, preferably before the code police arrive :(