From: | "Josh Berkus" <josh(at)agliodbs(dot)com> |
---|---|
To: | Brad Hilton <bhilton(at)vpop(dot)net>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: switching from mysql |
Date: | 2001-11-16 18:30:42 |
Message-ID: | web-507719@davinci.ethosmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-16 19:35:22 | Re: switching from mysql |
Previous Message | Brett W. McCoy | 2001-11-16 18:03:38 | Re: Perl and pgsql... |