Re: sql query help needed!!!

Hi.

 Try:

 SELECT act.student_id, ch.change_desc,
  DECODE (act.change_id,

   1,'FROM ' || sb1.subject_name || ' TO ' || sb2.subject_name,
   2, NULL,
   4, 'MOVED SCHOOL TO ' || sch.school_name,

 FROM tbl_student_activity act,
      tbl_subject sb1,
      tbl_subject sb2,
      tbl_school sch

  act.change_id IN ( 1,2,4 ) AND
  act.subject_id_from = sb1.subject_id ( + ) AND
  act.subject_id_to   = sb2.subject_id ( + ) AND
  act.school_id       = sch.schoool_id ( + );

 HTH. Michael.

In article <YNOJ4.1343$1D.12289_at_news-server.bigpond.net.au>,   "Cobra__77" <Cobra__77_at_hotmail.com> wrote:
> can someone suggest a solution to this..thanks
>
> I have the following tables
>
> 1. tbl_subject has
> subject_id subject_name
> 1 Biology
> 2 Chemistry
> 3 Maths
>
> 2. tbl_school has
> school_id school_name
> 1 Arthur high
> 2 NY high
> 3 MS high
>
> 3. tbl_change has
> change_id change_desc
> 1 changed subject
> 2 left - unknown
> 3 New enrolment
> 4 changed school
>
> 4. tbl_student_activity has
> activity_id student_id change_id subject_id_From subject_id_To
 school_id
> 1 1000 2 null
 null
> null
> 2 1001 1 1 2
> null
> 3 1002 4 null
 null
> 1
>
> I want to query the tbl_student_activity table and it should return
 the
> following:
> I can only use sql to do this...so PL/SQL is not an option :-(
>
> 1000 left - unknown
> 1001 Changed subject Biology - Chemisty
> 1002 moved school to Arthur high
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Apr 15 2000 - 00:00:00 CDT