Re: SQL Query gone wrong.. please help - Mailing list pgsql-novice

From Srinivas Iyyer
Subject Re: SQL Query gone wrong.. please help
Date
Msg-id 20060209175031.60367.qmail@web34502.mail.mud.yahoo.com
Whole thread Raw
In response to Re: SQL Query gone wrong.. please help  (Murat Tasan <murat.tasan@cwru.edu>)
Responses Re: SQL Query gone wrong.. please help
List pgsql-novice
Hi,
thank you for correcting me.  I agree, I am very weak
in connecting tables, I did not digest the connecting
logic, although based on my progra. experience i am
able to write plpgsql functions etc not relating to
sql query part.
I am trying to learn from Bruce momjian's book.

I have one more question, hope that is not problem for
you to help me out again.

How do I put another conditional that gos.go_cat = 'F'
only.

In the corrected query, I am getting all gos.go_term
but I want only if gos.go_cat is 'F'.


My version:

select seq_name , go_term,go_cat from sequence, gos,
go_seq where
            sequence.seq_name = 'EI24' AND
            gos.go_cat = 'F'AND
            sequence.seq_id = go_seq.seq_id AND
            go_seq.gos_id = gos.go_id AND
            gos.gos_id = gos.go_cat;




Thanks.
Sri





--- Murat Tasan <murat.tasan@cwru.edu> wrote:

>
> On 9 Feb 2006, at 12:08, Srinivas Iyyer wrote:
>
> > Dear group,
> >
> > I hav  e 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
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

pgsql-novice by date:

Previous
From: "Lane Van Ingen"
Date:
Subject: How to Include Contributed Modules in Automated Install
Next
From: Murat Tasan
Date:
Subject: Re: SQL Query gone wrong.. please help