Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #12697

Re: looking for an appropriate query

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>

Show all headers | View raw


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


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