problem with ambiguous field names (mysql 4.1)
Posted: Wed Feb 07, 2007 4:38 pm
feyd | Please use
The following error is returned:
If needed, the structure of the tables concerned is as follows: (lifted from mysqladmin backup)
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]
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;Does anyone know any workarounds for this problem? (It certainly is an annoying one, though I'm probably overlooking something simple)Column 'date' in field list is ambiguous
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]