Skip site navigation (1) Skip section navigation (2)

Re: switching from mysql

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 (view raw or flat)
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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-11-16 19:35:22
Subject: Re: switching from mysql
Previous:From: Brett W. McCoyDate: 2001-11-16 18:03:38
Subject: Re: Perl and pgsql...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group