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.