Just recently (possibly since upgrading to MySQL 5.0.13 RC), I've been
getting the following error with queries like the one below.
Unknown column 'photos.id' in 'on clause' (1054)
SELECT photos.*
FROM photos
LEFT JOIN tags_photos ON tags_photos.photo = photos.id
WHERE 1
AND tags_photos.tag IN (6)
AND NOT EXISTS (
SELECT 1
FROM tags AS t
LEFT OUTER JOIN tags_photos AS t2 ON (
t.id = t2.tag AND t2.photo = photos.id
)
WHERE t.id IN (6)
AND t2.tag IS NULL
)
GROUP BY photos.id
This was working previously, and there is definitely an `id` column in
the `photos` table. The table definitions follow:
CREATE TABLE `photos` (
`id` int(10) unsigned NOT NULL auto_increment,
`filename` varchar(100) NOT NULL,
`caption` varchar(200) NOT NULL,
`description` text NOT NULL,
`user` int(10) unsigned NOT NULL default '0',
`exif` longtext NOT NULL,
`uploaded` int(10) unsigned NOT NULL default '0',
`modified` int(10) unsigned NOT NULL,
`privacy`
enum('public','friends-family','friends','family','private') NOT NULL
default 'public',
`rights` enum('copyright','creative-commons','public-domain') NOT
NULL default 'copyright',
`notes` text NOT NULL,
`offensive` tinyint(1) unsigned NOT NULL,
`resizeMode` varchar(5) NOT NULL,
`allowOriginal` tinyint(1) NOT NULL default '1',
`licenseURL` varchar(200) NOT NULL,
`licenseName` varchar(50) NOT NULL,
`licenseButton` varchar(250) NOT NULL,
PRIMARY KEY (`id`),
KEY `user` (`user`),
KEY `uploaded` (`uploaded`),
KEY `privacy` (`privacy`),
KEY `modified` (`modified`),
CONSTRAINT `photos_ibfk_1` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `tags_photos` (
`tag` int(10) unsigned NOT NULL default '0',
`photo` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`tag`,`photo`),
KEY `photo` (`photo`),
CONSTRAINT `tags_photos_ibfk_1` FOREIGN KEY (`tag`) REFERENCES `tags`
(`id`),
CONSTRAINT `tags_photos_ibfk_2` FOREIGN KEY (`photo`) REFERENCES
`photos` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `tags` (
`id` int(10) unsigned NOT NULL auto_increment,
`tagName` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
