"Josh Berkus" <josh@agliodbs.com> writes:
> However, you should consider that neither MySQL's implementation of
> GROUP BY, nor SELECT DISTINCT ON, is ANSI-compliant SQL. Therefore
> portablility of either approach is limited.
A spec-compliant way to do it (assuming I've correctly understood
Brad's problem) is:
select items.* from items where
exists(select 1 from store_items where items.id = store_items.item_id);
However this has got performance problems in current Postgres releases
(soluble problems, but not solved yet), and I'm not sure if it works
at all in current MySQL releases --- I know they're working on subselect
support, but not sure if it's in any stable release yet.
So you're kinda stuck with one of the nonstandard approaches :-(
regards, tom lane