Re: Question on Select Statement

From: Tomm Carr <carr%tw_at_edwards.af.mil>
Date: 1997/08/21
Message-ID: <33FCD1F9.5C9E@edwards.af.mil>#1/1

Chinh Nguyen wrote:
>
> I have a master/detail relationship tables as below:
>
> create table Master(PrimKey number, Otherkey varchar2(20));
> insert into Master values (1,'HELLO');
> insert into Master values (2,'BYE');
>
> create table Detail(ForeignKey number, column1 number);
> insert into Detail values (1, 200);
> insert into Detail values (1, 300);
> insert into Detail values (1, 400);
>
> Can I construct a select statement that will return "one and only one"
> row containing all detail for this tables relationship.
>
> The idea result row shoud look like:
>
> 1 HELLO 200 300 400
select PrimKey, OtherKey, d1.column1, d2.column1, d3.column1 from Master m, Detail d1, Detail d2, Detail d3

where PrimKey = d1.ForeignKey
  and PrimKey = d2.ForeignKey
  and PrimKey = d3.ForeignKey

  and d2.column1 > d1.column1
  and d3.column1 > d2.column1

This, of course, is not at all robust. It will only work for those Master entries with exactly three, unique Detail entries. There are changes you can make so it will work with three or less, but it would be horrendously complex and you would still have problems if there were *more than* three entries.

Personally, I think you are trying to be a *little* too fancy and making SQL do what it wasn't meant to do. Use other front-end tools to represent the data in fancy formats and use SQL to retrieve the data as efficiently as it can.

-- 
Tomm Carr  --> Note new e-mail address
tommcatt@computer.org -- http://www.geocities.com/athens/delphi/6769
-- The Macintosh computer is like the open range;
-- In a world without fences, there are no Gates.

Received on Thu Aug 21 1997 - 00:00:00 CDT