Re: JOIN question

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