Re: Difficult select statement

On Wed, 24 Sep 2003, Owen Funkhouser wrote:

> I have two tables.
>
> Table "data" has three fields, "id integer", "project text", and "component text".
>
> Table "participants" has two fields, "id integer" and "username text".
>
> The id in each table correspond to the same thing.
>
> The values for each table is as follows:
>
> data
> ----
> 1  |  Stocks  |  Decisions
> 2  |  Stocks  |  Loss
> 3  |  Stocks  |  Profits
> 4  |  Bonds   |  Interest
>
> participants
> ------------
> 1  |  George
> 1  |  Harry
> 1  |  Carmen
> 2  |  Owen
> 2  |  John
> 3  |  Harry
> 4  |  Bubba
>
> What I want is a distinct list of id, project, and component where username = Harry.
> Resulting in the following:
>
> 1  |  Stocks  |  Decisions
> 3  |  Stocks  |  Profits

I'd think something like:

select distinct id, project, component from data inner join participants
using (id) where username='Harry';

will give you what you want if I'm understanding the request correctly.
Your example data doesn't seem to have any cases where the distinct
matters, but I assume the real data does.  If it doesn't (like there's a
unique constraint on data.id and a two column unique constraint on id,
username for participants), you don't need the distinct in the query.