Received: from post.webmailer.de (natpost.webmailer.de [192.67.198.65])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id fBMNtim08174
	for <pgsql-sql@postgresql.org>; Sat, 22 Dec 2001 18:55:45 -0500 (EST)
	(envelope-from barwick@gmx.net)
Received: from there (pD9EB09C6.dip.t-dialin.net [217.235.9.198])
	by post.webmailer.de (8.9.3/8.8.7) with SMTP id AAA29294;
	Sun, 23 Dec 2001 00:55:43 +0100 (MET)
Message-Id: <200112222355.AAA29294@post.webmailer.de>
Content-Type: text/plain;
  charset="iso-8859-1"
From: Ian Barwick <barwick@gmx.net>
To: "Frank Morton" <fmorton@base2inc.com>, <pgsql-sql@postgresql.org>
Subject: Re: JOIN question
Date: Sat, 22 Dec 2001 23:57:34 +0100
X-Mailer: KMail [version 1.3.1]
References: <web-533913@davinci.ethosmedia.com>
	<000d01c18b1e$9ba84320$8455e5ce@edison>
In-Reply-To: <000d01c18b1e$9ba84320$8455e5ce@edison>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200112/243
X-Sequence-Number: 6065

On Saturday 22 December 2001 20:26, Frank Morton wrote:
> I'm looking for the most portable way to do the following,
> given these two tables:
>
> Table 1 is called "content" which contains an integer "id" column.
> The value of this id is "1" for this example.
>
> Table 2 is called "protection" and keeps track of who can read
> and write the content object, so this table may contain multiple
> protection settings for a single content object.
>
> Simplifying, to consider my problem, the protection table contains
> a column called "contentId", connecting it to the content table id.
> This table has three rows in it for three groups that can read it
> with contentId set to "1".
>
> If I do the query:
>
> select Content.* from Content,Protection
> where (Content.id = Protection.contentId);
>
> I get three rows back, corresponding to each group that has
> access to the content. However, I would like to get back
> just one row, corresponding to the content that fits the desired
> protections.

IIUC (if I understand correctly) you want to get each row of table 
"Content" which is referenced at least once from "Protection"?

If so SELECT DISTINCT will be your friend:

SELECT DISTINCT Content.* 
           FROM Content,Protection
          WHERE (Content.id = Protection.contentId)

or in ANSI rather than theta join style:

SELECT DISTINCT Content.* 
           FROM Content
     INNER JOIN Protection ON (Content.id=Protection.contentId)

You could achieve the same result with a subselect, although
it may be slower:

SELECT * 
  FROM Content
 WHERE Content.id IN (SELECT contentID FROM Protection)

HTH

Ian Barwick