 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
jacques
Guest
|
 Posted: Tue Jun 13, 2006 6:07 am Post subject: Inner join problem |
 |
|
I'm trying to retrieve data with ADOQuery component. The problem is that when i run the code the same information is retrieved three times. I've created a database to store newsgroup data:
host tble stores all hostname data, and the group tbl stores all the information about groupnames relating to the host, in this particular case, there is one host and three groups. So there should only be one host displayd with the three groups as children:
host
group1
group2
group3
instead i get this:
host
group1
host
group2
host
group3
here's the code
q.close;
q.SQL.Text:='SELECT host.hostID,host.name,group.hID, group.name from [host],[group] WHERE host.hostID=group.hID ';
q.Open;
q.First;
while not q.Eof do begin
with groups.Items.AddFirst(nil, q.fieldbyname('host.name').Text) do
begin
Selected := true;
groups.Items.AddChild(groups.Selected,q.fieldbyname('group.name').Text)
end;
end;
q.Next;
end;
Hope someone can help with this
Thanks |
|
| Back to top |
|
 |
Brian Bushay TeamB
Guest
|
| Posted: Tue Jun 13, 2006 8:11 am Post subject: Re: Inner join problem |
|
|
| Quote: | I'm trying to retrieve data with ADOQuery component. The problem is that when i run the code the same information is retrieved three times. I've created a database to store newsgroup data:
host tble stores all hostname data, and the group tbl stores all the information about groupnames relating to the host, in this particular case, there is one host and three groups. So there should only be one host displayd with the three groups as children:
host
group1
group2
group3
instead i get this:
host
group1
host
group2
host
group3
here's the code
q.close;
q.SQL.Text:='SELECT host.hostID,host.name,group.hID, group.name from [host],[group] WHERE host.hostID=group.hID ';
q.Open;
q.First;
while not q.Eof do begin
with groups.Items.AddFirst(nil, q.fieldbyname('host.name').Text) do
begin
Selected := true;
groups.Items.AddChild(groups.Selected,q.fieldbyname('group.name').Text)
end;
end;
q.Next;
end;
|
Your While loop needs to skip adding Host.name when host name is the same as the
prior records. So that for each record where host.name has not changed only
child records are added
--
Brian Bushay (TeamB)
Bbushay (AT) NMPLS (DOT) com |
|
| Back to top |
|
|
jacques
Guest
|
| Posted: Tue Jun 13, 2006 6:53 pm Post subject: Re: Inner join problem |
|
|
That only works if i have one host when i added a new host the situation remained the same as before.
thanks
Brian Bushay TeamB <BBushay (AT) Nmpls (DOT) com> wrote:
| Quote: |
I'm trying to retrieve data with ADOQuery component. The problem is that when i run the code the same information is retrieved three times. I've created a database to store newsgroup data:
host tble stores all hostname data, and the group tbl stores all the information about groupnames relating to the host, in this particular case, there is one host and three groups. So there should only be one host displayd with the three groups as children:
host
group1
group2
group3
instead i get this:
host
group1
host
group2
host
group3
here's the code
q.close;
q.SQL.Text:='SELECT host.hostID,host.name,group.hID, group.name from [host],[group] WHERE host.hostID=group.hID ';
q.Open;
q.First;
while not q.Eof do begin
with groups.Items.AddFirst(nil, q.fieldbyname('host.name').Text) do
begin
Selected := true;
groups.Items.AddChild(groups.Selected,q.fieldbyname('group.name').Text)
end;
end;
q.Next;
end;
Your While loop needs to skip adding Host.name when host name is the same as the
prior records. So that for each record where host.name has not changed only
child records are added
--
Brian Bushay (TeamB)
Bbushay (AT) NMPLS (DOT) com |
|
|
| Back to top |
|
|
jacques
Guest
|
| Posted: Tue Jun 13, 2006 8:04 pm Post subject: Re: Inner join problem |
|
|
Sorted it, i was placing the currentparent variable at the wrong place. heres the working code:
q.close;
q.SQL.Text:='SELECT host.hostID,host.name,group.hID, group.name from [host],[group] WHERE host.hostID=group.hID ORDER by [group.hID]';
q.Open;
q.First;
CurrentParent:='';
while not q.Eof do begin
if q.fieldbyname('host.name').Text <> currentparent then begin
with groups.Items.AddFirst(nil, q.fieldbyname('host.name').Text) do begin
selected:=true;
end;
CurrentParent:=q.fieldbyname('host.name').Text;
end;
groups.Items.AddChild(groups.Selected,q.fieldbyname('group.name').Text);
q.Next;
end;
Thanks for all your help
jacques
"jacques" <jacquesx (AT) gmail (DOT) com> wrote:
| Quote: |
That only works if i have one host when i added a new host the situation remained the same as before.
thanks
Brian Bushay TeamB <BBushay (AT) Nmpls (DOT) com> wrote:
I'm trying to retrieve data with ADOQuery component. The problem is that when i run the code the same information is retrieved three times. I've created a database to store newsgroup data:
host tble stores all hostname data, and the group tbl stores all the information about groupnames relating to the host, in this particular case, there is one host and three groups. So there should only be one host displayd with the three groups as children:
host
group1
group2
group3
instead i get this:
host
group1
host
group2
host
group3
here's the code
q.close;
q.SQL.Text:='SELECT host.hostID,host.name,group.hID, group.name from [host],[group] WHERE host.hostID=group.hID ';
q.Open;
q.First;
while not q.Eof do begin
with groups.Items.AddFirst(nil, q.fieldbyname('host.name').Text) do
begin
Selected := true;
groups.Items.AddChild(groups.Selected,q.fieldbyname('group.name').Text)
end;
end;
q.Next;
end;
Your While loop needs to skip adding Host.name when host name is the same as the
prior records. So that for each record where host.name has not changed only
child records are added
--
Brian Bushay (TeamB)
Bbushay (AT) NMPLS (DOT) com
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
