Re: sql problem (hope it's not too easy!)

I believe you need something like this:

select t2.cust_no, t1.feecode, t1.feedesc, decode(t2.feecd,t1.feecode,t2.yesno,null) YesNo from t2, t1
order by t2.cust_no, t1.feecode

Note the absence of a where clause. This is one instance where you need that matrix where every row in t1 references every row in t2.

At 07:46 AM 12/22/00 -0800, you wrote:
>Because if it is, I'll feel foolish.
>
>I've been struggling with this one for a bit, and if there's an answer,
>it's escaped me.
>
>Given the following (very simplified) tables:
>
>T1:
>
>FEECODE varchar2(10)
>FEEDESC varchar2(50)
>
>constraint T1_PK primary key (FEECODE)
>
>
>T2:
>
>CUSTNO number(9)
>FEECD varchar2(10) (references T1.FEECODE)
>YESNO number(1)
>
>constraint T2_PK primary key (CUSTNO,FEECD)
>
>For any CUSTNO, FEECD is a value which is *always* found in T1 (FEECODE).
>However, for any CUSTNO, all FEECODE's don't necessarily exist in T2. I'd
>like to be able to get an sql output, for any given CUSTNO, showing CUSTNO,
>FEECODE/FEECD (all possible) ,DESC,YESNO where YESNO is either null or some
>default if there isn't an occurrence of CUSTNO+FEECODE in T2.
>
>So far, everything I've tried results in a list with just the T2 items that
>actually exist. I'd like to avoid some monstrous PL/SQL concoction. Is this
>possible? (I'm sure it is, and it'll be embarrassingly easy).
>
>---
>Dennis Taylor
>---
>Don't worry about people stealing your ideas. If your ideas
>are any good,
>you'll have to ram them down people's throats.
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Dennis Taylor
> INET: ismgr_at_pctc.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Received on Fri Dec 22 2000 - 11:51:09 CST