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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco