Re: Can I do this with SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 01 Mar 1999 22:24:17 GMT
Message-ID: <36db12e8.21779587@inet16.us.oracle.com>

On Mon, 01 Mar 1999 14:37:00 -0500, DelphiDev <DelphiDev_at_yahoo.com> wrote:

>Consider the two tables:
>
>PERSONEL
>ID POSITION BUILDING NAME
>003 VP 01 Smith
>004 PRES 02 Jones
>005 VP 02 Gates
>006 PROF 01 Ford
>007 PRES 01 Kenneth
>
>
>CODES
>CODEQUAL CODE DESC
>BUILDING 01 Building I
>BUILDING 02 Building II
>POSITION VP Vice President
>POSITION PRES President
>POSITION PROF Professor
>
>What would be the applicable SQL context if I wanted a list of the name
>from PERSONEL, and the descriptions are the building and position?
>I hope I don't have to break up my "CODES" table into a bunch of smaller
>ones. The reason it was done this was because it was ported from a file
>based system and done to keep the number of files to a minimum.
>
>Thanks, Stan.

try this...

  1 select
  2 p.id,
  3 p.name,
  4 c1.description position,
  5 c2.description building
  6 from personel p, codes c1, codes c2

  7  where c2.code = p.building
  8    and c2.codequal = 'BUILDING'
  9    and c1.code = p.position
 10*   and c1.codequal = 'POSITION'

SQL> /         ID NAME POSITION BUILDING

---------- --------------- -------------------- --------------------
         7 Kenneth         President            Building I
         4 Jones           President            Building II
         6 Ford            Professor            Building I
         3 Smith           Vice President       Building I
         5 Gates           Vice President       Building II

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 01 1999 - 16:24:17 CST