Outer Joins for EDM Problem

From: Fred Smith <smith>
Date: 1995/10/03
Message-ID: <44secc$k32_at_sndsu1.sedalia.sinet.slb.com>#1/1


I have a problem characterized by the following tables.

create table book (
title varchar2(20)
);

create table subject (

title	varchar2(20) references book(title),
keyword	varchar2(20)

);

I would like to have a flat view that would be easy to intersect keyword queries. Something like...

create	view book_cat (title, keyword1, keyword2) as
select	b.title, k1.keyword, k2.keyword
from	book b, subject k1, subject k2
where	b.title = k1.title (+) and
	b.title = k2.title (+);


Then I can make queries like...

select	title
from	book_cat
where	keyword1='Mexican' and keyword2='Food';


It works pretty well; finds the books I want and the keywords are not dependent on order. The only problem is that if there is a book with only one keyword, in the book_cat view, the keyword is repeated in both keyword fields.

My question is two-fold:
1. Is the view really doing what I want? Could I ignore the repeated

   keywords?
2. From a UI point of view, it would be nice to filter the repetitions.

   Can this be done in the view without breaking its query power?

I would actually like to support three keywords in the view. In that case the repetitions are really aggravating. Does anyone know any slick tricks?

Thanks in advance.

Fred Smith
713/513-2399
fsmith_at_houston.geoquest.slb.com Received on Tue Oct 03 1995 - 00:00:00 CET

Original text of this message