im trying to find records where 3 fields contain the same data.

for example
fields "title", "edition", "volume", "project"
data "Farm Animals", "2", "1", "Cow"
data "Farm Animals", "2", "1", "Horse"
data "Farm Animals", "2", "1", "Sheep"
data "Farm Animals", "2", "1", "Goat"

i want to put data "Farm Animals", "2", "1" into a tree
and Cow, Horse, Sheep & Goat as sub items under it.

any tips to this thanx.

The first step is to get a count by your groups

1) Select fn1, fn2, fn3, count(*) as NumRecs
Into Temp
from Table
group by 1,2,3
having NumRecs > 1;

Then create your parent/child relationships for your tree.  You may still
want to keep the header record even if there is only
one record if you want all your data to appear correctly ... Anyway, a step
to get you going...

Donat.

"Aaron Taylor" <tayloredwarez@optusnet.com.au> wrote in message
news:9B3ACB25-A857-45CD-B40E-B8DB19550872@news.elevatesoft.com...
> im trying to find records where 3 fields contain the same data.
>
> for example
> fields "title", "edition", "volume", "project"
> data "Farm Animals", "2", "1", "Cow"
> data "Farm Animals", "2", "1", "Horse"
> data "Farm Animals", "2", "1", "Sheep"
> data "Farm Animals", "2", "1", "Goat"
>
> i want to put data "Farm Animals", "2", "1" into a tree
> and Cow, Horse, Sheep & Goat as sub items under it.
>
> any tips to this thanx.
>

"Aaron Taylor" <tayloredwarez@optusnet.com.au> wrote in message
news:9B3ACB25-A857-45CD-B40E-B8DB19550872@news.elevatesoft.com...
> im trying to find records where 3 fields contain the same data.
>
> for example
> fields "title", "edition", "volume", "project"
> data "Farm Animals", "2", "1", "Cow"
> data "Farm Animals", "2", "1", "Horse"
> data "Farm Animals", "2", "1", "Sheep"
> data "Farm Animals", "2", "1", "Goat"
>
> i want to put data "Farm Animals", "2", "1" into a tree
> and Cow, Horse, Sheep & Goat as sub items under it.
>
> any tips to this thanx.
>

several ways to do this, I guess One would be to create the master table

select distinct title, edition, volume into mastertable;
create index bytitle on mastertable (title, edition, volume);

and then use your current table as the detail table. Quickanddirty, but
should work, sort of.

a bit more refined,

DROP TABLE IF EXISTS MEMORY\MASTER;
DROP TABLE IF EXISTS MEMORY\DETAIL;
SELECT DISTINCT title, edition, volume INTO MEMORY\MASTER FROM MYTABLE;
ALTER TABLE MEMORY\MASTER ADD MKEY AUTOINC;
CREATE INDEX MINDEX ON MEMORY\MASTER (MKEY);
/* NOW CREATE THE DETAIL TABLE */
SELECT PROJECT, MKEY FOREIGNKEY INTO MEMORY\DETAIL FROM MYTABLE T1
JOIN MEMORY\MASTER T2 ON (T1.TITLE = T2.TITLE) AND (T1.EDITION = T2.EDITION)
AND (T1.VOLUME = T2.VOLUME);
CREATE INDEX FINDEX ON MEMORY\DETAIL (FOREIGNKEY);

Robert

Aaron


Basically you can't do what you want in sql. You can use a mix of sql and navigation to achieve the result. I've dumped below the code I use to populate the tree for my mail and news reader.

Your example shows a simple tree so what I'd do is:

1. Create a query to do a SELECT DISTINCT "title", "edition", "volume" from table
2. walk down that result set creating a top level node for each record
    a) for each record AS YOU WALK DOWN THE RESULT SET run a second query to get the project (eg SELECT "project" from table WHERE "title" = query1.title and "edition" = query1."edition" and volume = quer1.volume) and create a node under the top level node.

Not difficult but you can't do it in sql

Roy Lambert


procedure PopulateMailBoxKids(WhichTree: TElTree; Node: TElTreeItem);
var
Child: TElTreeItem;
procedure AddExtraColumns;
begin
 Child.ColumnText.Add(IntToStr(mnd.luBandA_BoxNo.AsInteger));
 case mnd.luBandA_BoxNo.AsInteger of
  BoxIn: Child.ImageIndex := 38;
  BoxHold: Child.ImageIndex := 35;
  BoxQueued: Child.ImageIndex := 37;
  BoxSent: Child.ImageIndex := 39;
  BoxUnknown: Child.ImageIndex := 41;
  BoxTrash: Child.ImageIndex := 10;
  BoxFailed: Child.ImageIndex := 72;
  BoxSpam: Child.ImageIndex := 24;
  BoxSuspicious: Child.ImageIndex := 74;
 else if mnd.luBandA_IsFolder.AsBoolean then Child.ImageIndex := MsgFolder else Child.ImageIndex := MsgBox;
 end;
 if mnd.luBandA_Unread.AsInteger > 0 then begin
  Child.ParentStyle := False;
  Child.Bold := True;
 end;
end;
begin
mnd.luBandA.Filter := '_BelongsTo = ' + Node.ColumnText[0];
mnd.luBandA.Filtered := True;
mnd.luBandA.First;
while (not mnd.luBandA.Eof) do begin
 Child := WhichTree.Items.AddChild(node, mnd.luBandA_BoxName.AsString);
 AddExtraColumns;
 mnd.luBandA.Next;
end;
Child := Node.GetFirstChild;
while Child <> nil do begin
 PopulateMailBoxKids(WhichTree, Child);
 Child := Child.GetNextSibling;
end;
end;

procedure PopulateMailBoxTree(WhichTree: TElTree; Mode: Char);
var
etNode: TElTreeItem;
Qry: TDBISAMQuery;
begin
Qry := MakeDBISAMQuery(slOptsStr('_DataPath'), mnd.MainSession);
Qry.SQL.Add('SELECT _BoxNo, _BoxName,_Unread, _IsFolder from "BandA"');
if Mode = 'M' then begin
 Qry.SQL.Add('WHERE _BoxType = ''M'' AND _BelongsTo = 0');
 Qry.SQL.Add('ORDER BY _BoxName');
end else begin
 Qry.SQL.Add('WHERE _BoxType = ''P'' AND _BoxNo < 0 AND _BelongsTo = 0');
 Qry.SQL.Add('ORDER BY _BoxNo DESC');
end;
Qry.RequestLive := True;
Qry.ExecSQL;
Qry.Last;
while (not Qry.Bof) do begin
 etNode := WhichTree.Items.AddFirst(nil, Qry.FieldByName('_BoxName').AsString);
 etNode.ColumnText.Add(Qry.FieldByName('_BoxNo').AsString);
 if Qry.FieldByName('_IsFolder').AsBoolean then etNode.ImageIndex := MsgFolder else etNode.ImageIndex := MsgBox;
 if Qry.FieldByName('_Unread').AsInteger > 0 then begin
  etNode.ParentStyle := False;
  etNode.Bold := True;
 end;
 PopulateMailBoxKids(WhichTree, etNode);
 Qry.Prior;
end;
mnd.luBandA.Filtered := False;
mnd.luBandA.Filter := '';
Qry.Free;
end;

procedure FillMailBoxTree(WhichTree: TElTree);
procedure AddStandardBox(BoxName: string; BoxNo, Picy: integer);
begin
 if not mnd.luBandA.Locate('_BoxNo', BoxNo, []) then begin
  mnd.luBandA.Insert;
  mnd.luBandA_BoxNo.AsInteger := BoxNo;
  mnd.luBandA_BoxName.AsString := BoxName;
  mnd.luBandA_BelongsTo.AsInteger := 0;
  mnd.luBandA_IsFolder.AsBoolean := False;
  mnd.luBandA_DownloadHigh.AsInteger := 0;
  mnd.luBandA_Unread.AsInteger := 0;
  mnd.luBandA_Active.AsBoolean := True;
  mnd.luBandA_BoxType.AsString := 'P';
  mnd.luBandA.Post;
 end;
end;
begin
mnd.luBandA.Refresh;
WhichTree.Items.Clear;
// Make sure all the standard mailboxes exist
AddStandardBox('Trash', BoxTrash, 10);
AddStandardBox('Spam', BoxSpam, 24);
AddStandardBox('Suspicious', BoxSuspicious, 74);
AddStandardBox('Unknown', BoxUnknown, 41);
AddStandardBox('Failed', BoxFailed, 72);
AddStandardBox('Sent', BoxSent, 39);
AddStandardBox('Queued', BoxQueued, 37);
AddStandardBox('Hold', BoxHold, 35);
AddStandardBox('In', BoxIn, 38);
PopulateMailBoxTree(WhichTree, 'M');
PopulateMailBoxTree(WhichTree, 'P');
end;

You can build it in SQL by joining each sub-level back to its parent  with
an update prior to insertion into the final table.
ie If they are joined by three fields, create the header with the autoinc as
noted, then join the children back to the header
ie Set ParentID = P.RowID based on the join criteria.   For fixed levels, a
person wouldn't even need to create a construct within Delphi.
For more complex or variable, a construct is required for the ranges...
Donat.


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:8220971D-32B4-4F11-84E7-6B9A05400A77@news.elevatesoft.com...
> Aaron
>
>
> Basically you can't do what you want in sql. You can use a mix of sql and
> navigation to achieve the result. I've dumped below the code I use to
> populate the tree for my mail and news reader.
>
> Your example shows a simple tree so what I'd do is:
>
> 1. Create a query to do a SELECT DISTINCT "title", "edition", "volume"
> from table
> 2. walk down that result set creating a top level node for each record
> a) for each record AS YOU WALK DOWN THE RESULT SET run a second query to
> get the project (eg SELECT "project" from table WHERE "title" =
> query1.title and "edition" = query1."edition" and volume = quer1.volume)
> and create a node under the top level node.
>
> Not difficult but you can't do it in sql
>
> Roy Lambert
>
>
> procedure PopulateMailBoxKids(WhichTree: TElTree; Node: TElTreeItem);
> var
> Child: TElTreeItem;
> procedure AddExtraColumns;
> begin
> Child.ColumnText.Add(IntToStr(mnd.luBandA_BoxNo.AsInteger));
> case mnd.luBandA_BoxNo.AsInteger of
> BoxIn: Child.ImageIndex := 38;
> BoxHold: Child.ImageIndex := 35;
> BoxQueued: Child.ImageIndex := 37;
> BoxSent: Child.ImageIndex := 39;
> BoxUnknown: Child.ImageIndex := 41;
> BoxTrash: Child.ImageIndex := 10;
> BoxFailed: Child.ImageIndex := 72;
> BoxSpam: Child.ImageIndex := 24;
> BoxSuspicious: Child.ImageIndex := 74;
> else if mnd.luBandA_IsFolder.AsBoolean then Child.ImageIndex := MsgFolder
> else Child.ImageIndex := MsgBox;
> end;
> if mnd.luBandA_Unread.AsInteger > 0 then begin
> Child.ParentStyle := False;
> Child.Bold := True;
> end;
> end;
> begin
> mnd.luBandA.Filter := '_BelongsTo = ' + Node.ColumnText[0];
> mnd.luBandA.Filtered := True;
> mnd.luBandA.First;
> while (not mnd.luBandA.Eof) do begin
> Child := WhichTree.Items.AddChild(node, mnd.luBandA_BoxName.AsString);
> AddExtraColumns;
> mnd.luBandA.Next;
> end;
> Child := Node.GetFirstChild;
> while Child <> nil do begin
> PopulateMailBoxKids(WhichTree, Child);
> Child := Child.GetNextSibling;
> end;
> end;
>
> procedure PopulateMailBoxTree(WhichTree: TElTree; Mode: Char);
> var
> etNode: TElTreeItem;
> Qry: TDBISAMQuery;
> begin
> Qry := MakeDBISAMQuery(slOptsStr('_DataPath'), mnd.MainSession);
> Qry.SQL.Add('SELECT _BoxNo, _BoxName,_Unread, _IsFolder from "BandA"');
> if Mode = 'M' then begin
> Qry.SQL.Add('WHERE _BoxType = ''M'' AND _BelongsTo = 0');
> Qry.SQL.Add('ORDER BY _BoxName');
> end else begin
> Qry.SQL.Add('WHERE _BoxType = ''P'' AND _BoxNo < 0 AND _BelongsTo = 0');
> Qry.SQL.Add('ORDER BY _BoxNo DESC');
> end;
> Qry.RequestLive := True;
> Qry.ExecSQL;
> Qry.Last;
> while (not Qry.Bof) do begin
> etNode := WhichTree.Items.AddFirst(nil,
> Qry.FieldByName('_BoxName').AsString);
> etNode.ColumnText.Add(Qry.FieldByName('_BoxNo').AsString);
> if Qry.FieldByName('_IsFolder').AsBoolean then etNode.ImageIndex :=
> MsgFolder else etNode.ImageIndex := MsgBox;
> if Qry.FieldByName('_Unread').AsInteger > 0 then begin
> etNode.ParentStyle := False;
> etNode.Bold := True;
> end;
> PopulateMailBoxKids(WhichTree, etNode);
> Qry.Prior;
> end;
> mnd.luBandA.Filtered := False;
> mnd.luBandA.Filter := '';
> Qry.Free;
> end;
>
> procedure FillMailBoxTree(WhichTree: TElTree);
> procedure AddStandardBox(BoxName: string; BoxNo, Picy: integer);
> begin
> if not mnd.luBandA.Locate('_BoxNo', BoxNo, []) then begin
> mnd.luBandA.Insert;
> mnd.luBandA_BoxNo.AsInteger := BoxNo;
> mnd.luBandA_BoxName.AsString := BoxName;
> mnd.luBandA_BelongsTo.AsInteger := 0;
> mnd.luBandA_IsFolder.AsBoolean := False;
> mnd.luBandA_DownloadHigh.AsInteger := 0;
> mnd.luBandA_Unread.AsInteger := 0;
> mnd.luBandA_Active.AsBoolean := True;
> mnd.luBandA_BoxType.AsString := 'P';
> mnd.luBandA.Post;
> end;
> end;
> begin
> mnd.luBandA.Refresh;
> WhichTree.Items.Clear;
> // Make sure all the standard mailboxes exist
> AddStandardBox('Trash', BoxTrash, 10);
> AddStandardBox('Spam', BoxSpam, 24);
> AddStandardBox('Suspicious', BoxSuspicious, 74);
> AddStandardBox('Unknown', BoxUnknown, 41);
> AddStandardBox('Failed', BoxFailed, 72);
> AddStandardBox('Sent', BoxSent, 39);
> AddStandardBox('Queued', BoxQueued, 37);
> AddStandardBox('Hold', BoxHold, 35);
> AddStandardBox('In', BoxIn, 38);
> PopulateMailBoxTree(WhichTree, 'M');
> PopulateMailBoxTree(WhichTree, 'P');
> end;
>

thanx to all ....