Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #12697
| From | Franz Rachbauer <office@rachbauer.org> |
|---|---|
| Newsgroups | comp.lang.clipper.visual-objects |
| Subject | Re: looking for an appropriate query |
| Date | 2019-11-05 17:44 +0100 |
| Organization | Aioe.org NNTP Server |
| Message-ID | <qps8tb$1g5u$1@gioia.aioe.org> (permalink) |
| References | <5c7ab1cc-f22d-4d92-9676-0ded5e5625cc@googlegroups.com> <2a2394ae-63e7-4444-aeab-d4650d24426c@googlegroups.com> |
If you do it in SQL, then if given fields and relations: model (key modelid) 1:n components (keys modelid, compid) components (keys modelid, compid) 1:n materials (keys compid, fieldx) with in: select * from models where modelid in (select modid from components where compid in (select compid from materials where materials.fieldx = 'your material')) with exists: select * from models where exists (select 1 from components where models.modelid = components.modelid and exists (select 1 from materials where components.compid = materials.compid and materials.fieldx = 'your material')) Regards, Franz Am 05.11.2019 um 16:12 schrieb dlzc: > Dear jorge.dex...: > > On Tuesday, November 5, 2019 at 3:15:13 AM UTC-7, jorge.dex...@gmail.com wrote: > ... >> In simple terms .... >> What would a competent query look like? >> >> I have models >> The models have components >> The components have raw materials >> >> Given a raw material represent the models >> where this raw material participates.... > > It is called a "where used" report. Only these usually stop at "one level higher" (raw material to component). > > In general, you should have a "link list" or "bill of materials", that marries "raw materials", "components"(, and so on), into "models". > > So you'd do one query to match a given "raw material" into a list of "components", and then for each member of that "component" list, you'd search the link list for a list of "models" that use those components. > > Might be nice to sort the "model" list and remove duplicates (or total how many "components" return the same "model", for sensitivity analysis), before presenting it. > > Is that what you wanted? > > I've done this in xHarbour, but not VO, and used only two tables... a part number + description (+ more) table, and a "bill of materials" table (higher level part number, detail part number, quantity, find number are the only fields). So it will be largely gibberish to you... > > David A. Smith >
Back to comp.lang.clipper.visual-objects | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
looking for an appropriate query jorge.dextrasistemas@gmail.com - 2019-11-05 02:15 -0800
Re: looking for an appropriate query dlzc <dlzc1@cox.net> - 2019-11-05 07:12 -0800
Re: looking for an appropriate query Franz Rachbauer <office@rachbauer.org> - 2019-11-05 17:44 +0100
Re: looking for an appropriate query dlzc <dlzc1@cox.net> - 2019-11-05 09:16 -0800
Re: looking for an appropriate query Johan Nel <johan.nel@xsinet.co.za> - 2019-11-05 21:53 -0800
Re: looking for an appropriate query jorge.dextrasistemas@gmail.com - 2019-11-10 03:42 -0800
csiph-web