From: Aakash Bordia <a_bordia@hotmail.com>
Newsgroups: comp.databases.theory
Subject: Re: Another Theory Question...
Date: Fri, 08 Sep 2000 15:25:04 -0700
Organization: UIUC
Lines: 79
Message-ID: <39B96740.A66F6F62@hotmail.com>
References: <8p1qfs$e8f$1@chatta.samart.co.th>
Reply-To: a_bordia@hotmail.com
NNTP-Posting-Host: bordia.stl.ibm.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en


Yes this is a preety interesting problem and similar to BOM problems. There are
different syntax for writing recursive queries in SQL. I think you can search
on the net and you will get solutions for the database that you are working
with. I am a DB2 person and dont know if the WITH kind of recursive syntax is
correct for every db engine or not!

Nos Doughty wrote:

> Hi,
>
> You know how the same sort of patterns come up again and again when your
> programming, and you tend to just stick with the first hack that solves it
> until one day you sit down and go "i really should sort that" ?
>
> well this is one of those situations for me :)
>
> As a working programmer i obviously do a lot of database work (like 99%)
> As an OOP (c++ now delphi)  programmer my sql equally obviously sucks...
>
> call it a dislike of 70's tech but it leaves me writing code i know is
> ineffecient,
> peicing multiple query results together with program code and lots of
> temporary
> tables instead of using advanced sql
>
> so... it's time to address it :)
>
> as an exersize i have given myself the following problem:
>
> an inventory system that contains items with sub items that can be pulled
> out and sold seperately - a common enough real work example of this
> relationship.
>
> for simplicity lets use the following tables... they're fairly normalised
> and
> getting the info back out efficiently escapes me. All i can think of is a
> recursive function in program code.. but i'm sure theres a standard sql way
> to do this - maybe a sub-query or something.....
>
> TableItem
> PKey        Desc            Cost        Details-etc
> 1               BigPC         10000     <stuff>
> 2               CoolHD      1000       <stuff>
> 3               SuperMB    500         <stuff>
> 4               FastoCPU   1000       <stuff>
> 5               LotsaRAM  500         <etc>
>
> TableInventory
> PKey      InventID     Date            details-etc
> 1             1                00-00-00    <stuff>
>
> TableStock
> PKey      Item_Ref     Ivent_Ref    ContainedBy_Ref
> 1             1                 1                 NULL
> 2             2                 1                1
> 3             3                 1                1
> 4             4                 1                3
> 5             4                 1                3      <-- dual cpu :)
> 6             5                 1                3
>
> Anything with the same Inventory Number InventID would be one entity. I
> envision grouping without containing also... so it would be possible for
> invent_id's to be the same with containedby_ref being null - say maybe you
> get a free dustcover with a pc...
>
> now - what would be the sql to retrive this in a meaningfull way - is it
> possible to show the relationship (say how many levels deep an item is for
> example). I'm sort of imagining this with a html interface also, so the
> interface simplicity issues are paramount :)
>
> you see how non-sql programmers get in this sort of bind - it's like -oops-
> i overnormalized the damn thing again...
>
> If anyone can advise i'd appreciate it :)
>
> Cheers
>
> Nos Doughty