Thread: Fw: Difficult select statement

Fw: Difficult select statement

From
"Derrick Betts"
Date:
Try this:
 
select * from data where id in(select id from participants where username = 'Harry')
 
Derrick
 
----- Original Message -----
From: "Owen Funkhouser" <funk@highwaay.net>
Sent: Wednesday, September 24, 2003 6:15 PM
Subject: [NOVICE] Difficult select statement

> 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
>
> Can this be done in a single SQL statement?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>