Re: Another Theory Question...

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
Received on Sat Sep 09 2000 - 00:25:04 CEST