From: Ian Barwick <barwick@gmx.net>
To: Frank Morton <fmorton@base2inc.com>
To: pgsql-sql@postgresql.org
Subject: Re: JOIN question
Date: Sat, 22 Dec 2001 23:57:34 +0100
Message-ID: <200112222355.AAA29294@post.webmailer.de> (raw)
In-Reply-To: <000d01c18b1e$9ba84320$8455e5ce@edison>
References: <web-533913@davinci.ethosmedia.com>
<000d01c18b1e$9ba84320$8455e5ce@edison>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: barwick@gmx.net, fmorton@base2inc.com
Subject: Re: JOIN question
In-Reply-To: <200112222355.AAA29294@post.webmailer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox