Re: switching from mysql

Brad,

> The example I gave was a simple one to demonstrate my problem.  Let
> me
> give a better example.  Let's say I have 3 tables: items, stores, and
> store_items.  'store_items' maps items to stores.  If I want to
> select
> all items that are in at least one store, I currently do:
>
> select items.* from items, store_items where items.id =
> store_items.item_id group by items.id
>
> Is there a better way to do this query?

In postgreSQL, you would use SELECT DISTINCT ON:

SELECT DISTINCT ON items.id *
FROM items JOIN store_items ON items.id = store_items.item_id;

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.

-Josh

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco