Outer Joins for EDM Problem
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