Re: SQL Query gone wrong.. please help

On 9 Feb 2006, at 12:08, Srinivas Iyyer wrote:

> Dear group,
>
> I have three tables:
>
>
> table: sequence
> seq_id | seq_name | seq_refseq
>      1 | EI24     | NM_004879.3
>      2 | NDRG2    | NM_201540.1
>      3 | RARRES2  | NM_002889.2
>
>
>
> table: gos
> gos_id | go_id | go_term | go_class
> 11041 | GO:0006807 | nitrogen compound metabolism |P
> 11049 | GO:0004263 | chymotrypsin activity| F
>
> table : go_seq
>
>
> gos_id | seq_id
>   13819 |  11408
>   13819 |  11950
>   13819 |  12822
>   13819 |  11350
>
>
>
>
> I want to query go_seq table, giving seq_name = 'EI24'
>
>
> select seq_name, go_term
> from sequence, gos, go_seq
>
>
> where  go_seq.seq_id = go_seq.gos_id and
>            sequence.seq_id = sequence.seq_name and
>             sequence_seq_name = 'EI24';
>

you are not joining over the relationship table (go_seq) correctly.
you want:

WHERE
    sequence.seq_name = 'EI24' AND
    sequence.seq_id = go_seq.seq_id AND
    go_seq.gos_id = gos.gos_id;

what you previously had was wrong in two locations:
"sequence.seq_id = sequence.seq_name" (i assume no sequence ID will
ever be quite like it's name) and
"go_seq.seq_id = go_seq.gos_id" (i assume no GO id will have the same
ID as a sequence).


>
> I am wrong here,  unable to get it correctly.  Could
> you please help here.
>

unfortunately (by the looks of your query), your understanding of SQL
is fundamentally flawed, which will lead to future confusion and
trouble.  i'd recommend picking up a decent book on SQL, or checking
out some online tutorials.


> thanks
> srini


hope that helped a bit!

murat