(response interspersed)
Jasper Bryant-Greene <[EMAIL PROTECTED]> wrote on 10/17/2005 07:49:36 PM:
> 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
^
I suspect that the error isn't here -------------
> 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
^
it's here -----------------------------
> )
> 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
In this case, I don't think that the query parser is correctly parsing
your EXISTS subquery within the context of the outer query. It's probably
a bug and you should submit a test case for resolution. To get your query
to work in the mean time, don't use subqueries use joins. I *think* you
are looking for photos that are not tagged so here is how I would re-write
your query. Let me know if I guessed incorrectly.
SELECT DISTINCT photos.*
FROM photos
LEFT JOIN tags_photos tp
ON tp.photo = photos.id
LEFT JOIN tags t
on tp.tag = t.id
WHERE tags_photos.tag IN (6)
AND t.id IS NULL;
I also made a few editorial changes:
1) using a GROUP BY with no aggregate functions (sum(), avg(), min(),
max(), etc) is illogical as your results will be indeterminate. If you do
not want duplicate rows, you have two options: deduplicate your data or
use the DISTINCT modifier.
2) "WHERE 1" is always true so it plays no useful part in your WHERE
clause. I removed it.
3) Technically, the IN comparator should be used with lists of values and
an = should be used for comparing single values. However, I understand the
pattern you are using and single values are legal in an IN comparison is
allowed on MySQL (not so with other RDBMSs) so it can stay.
As I said above, if I didn't divine the correct question your query was
designed to answer, just fill me in and I will happily rewrite the query
to fit.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
